<?xml version="1.0" encoding="utf-8"?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" lang="en">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Chapter 9 Language Structure</title>
<link rel="stylesheet" href="mvl.css" type="text/css" />
<meta name="generator" content="DocBook XSL Stylesheets + chunker.py v1.9.2" />
<link rel="start" href="index.html" title="{book-title}" />
<link rel="up" href="" title="" />
<link rel="prev" href="optimization.html" title="Chapter 8 Optimization" />
<link rel="next" href="charset.html" title="Chapter 10 Character Sets, Collations, Unicode" />
</head>
<body bgcolor="white" text="black" link="#0000FF" vlink="#840084" alink="#0000FF">
<div class="navheader">
<table width="100%" summary="Navigation header">
<tr>
<th colspan="3" align="center">Chapter 9 Language Structure</th>
</tr>
<tr>
<td width="20%" align="left"><a accesskey="p" href="optimization.html">Prev</a> </td>
<th width="60%" align="center"></th>
<td width="20%" align="right"> <a accesskey="n" href="charset.html">Next</a></td>
</tr>
</table>
<hr>
</div>
<div class="chapter">
<div class="titlepage">
<div>
<div>
<h1 class="title"><a name="language-structure"></a>Chapter 9 Language Structure</h1>

</div>

</div>

</div>
<div class="toc">
<p><b>Table of Contents</b></p><dl class="toc"><dt><span class="section"><a href="language-structure.html#literals">9.1 Literal Values</a></span></dt><dd><dl><dt><span class="section"><a href="language-structure.html#string-literals">9.1.1 String Literals</a></span></dt><dt><span class="section"><a href="language-structure.html#number-literals">9.1.2 Numeric Literals</a></span></dt><dt><span class="section"><a href="language-structure.html#date-and-time-literals">9.1.3 Date and Time Literals</a></span></dt><dt><span class="section"><a href="language-structure.html#hexadecimal-literals">9.1.4 Hexadecimal Literals</a></span></dt><dt><span class="section"><a href="language-structure.html#bit-value-literals">9.1.5 Bit-Value Literals</a></span></dt><dt><span class="section"><a href="language-structure.html#boolean-literals">9.1.6 Boolean Literals</a></span></dt><dt><span class="section"><a href="language-structure.html#null-values">9.1.7 NULL Values</a></span></dt></dl></dd><dt><span class="section"><a href="language-structure.html#identifiers">9.2 Schema Object Names</a></span></dt><dd><dl><dt><span class="section"><a href="language-structure.html#identifier-qualifiers">9.2.1 Identifier Qualifiers</a></span></dt><dt><span class="section"><a href="language-structure.html#identifier-case-sensitivity">9.2.2 Identifier Case Sensitivity</a></span></dt><dt><span class="section"><a href="language-structure.html#identifier-mapping">9.2.3 Mapping of Identifiers to File Names</a></span></dt><dt><span class="section"><a href="language-structure.html#function-resolution">9.2.4 Function Name Parsing and Resolution</a></span></dt></dl></dd><dt><span class="section"><a href="language-structure.html#keywords">9.3 Keywords and Reserved Words</a></span></dt><dt><span class="section"><a href="language-structure.html#user-variables">9.4 User-Defined Variables</a></span></dt><dt><span class="section"><a href="language-structure.html#expressions">9.5 Expressions</a></span></dt><dt><span class="section"><a href="language-structure.html#comments">9.6 Comment Syntax</a></span></dt></dl>
</div>
<p>
    This chapter discusses the rules for writing the following elements
    of <a class="link" href="glossary.html#glos_sql" title="SQL">SQL</a> statements when using MySQL:
</p>
<div class="itemizedlist">
<ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>
        Literal values such as strings and numbers
      </p></li><li class="listitem"><p>
        Identifiers such as database, table, and column names
      </p></li><li class="listitem"><p>
        Keywords and reserved words
      </p></li><li class="listitem"><p>
        User-defined and system variables
      </p></li><li class="listitem"><p>
        Comments
</p></li></ul>
</div>
<div class="section">
<div class="titlepage">
<div>
<div>
<h2 class="title" style="clear: both"><a name="literals"></a>9.1 Literal Values</h2>

</div>

</div>

</div>
<div class="toc">
<dl class="toc"><dt><span class="section"><a href="language-structure.html#string-literals">9.1.1 String Literals</a></span></dt><dt><span class="section"><a href="language-structure.html#number-literals">9.1.2 Numeric Literals</a></span></dt><dt><span class="section"><a href="language-structure.html#date-and-time-literals">9.1.3 Date and Time Literals</a></span></dt><dt><span class="section"><a href="language-structure.html#hexadecimal-literals">9.1.4 Hexadecimal Literals</a></span></dt><dt><span class="section"><a href="language-structure.html#bit-value-literals">9.1.5 Bit-Value Literals</a></span></dt><dt><span class="section"><a href="language-structure.html#boolean-literals">9.1.6 Boolean Literals</a></span></dt><dt><span class="section"><a href="language-structure.html#null-values">9.1.7 NULL Values</a></span></dt></dl>
</div>
<a class="indexterm" name="idm140091700592976"></a><p>
      This section describes how to write literal values in MySQL. These
      include strings, numbers, hexadecimal and bit values, boolean
      values, and <code class="literal">NULL</code>. The section also covers
      various nuances that you may encounter when dealing with these
      basic types in MySQL.
</p>
<div class="section">

<div class="titlepage">
<div>
<div>
<h3 class="title"><a name="string-literals"></a>9.1.1 String Literals</h3>
</div>
</div>
</div>
<a class="indexterm" name="idm140091700589408"></a><a class="indexterm" name="idm140091700587920"></a><a class="indexterm" name="idm140091700586848"></a><a class="indexterm" name="idm140091700585360"></a><a class="indexterm" name="idm140091700583872"></a><a class="indexterm" name="idm140091700582384"></a><p>
        A string is a sequence of bytes or characters, enclosed within
        either single quote (<code class="literal">'</code>) or double quote
        (<code class="literal">"</code>) characters. Examples:
      </p><pre data-lang="sql" class="programlisting">
'a string'
"another string"
</pre><a class="indexterm" name="idm140091700578048"></a><a class="indexterm" name="idm140091700576976"></a><p>
        Quoted strings placed next to each other are concatenated to a
        single string. The following lines are equivalent:
      </p><pre data-lang="sql" class="programlisting">
'a string'
'a' ' ' 'string'
</pre><p>
        If the <a class="link" href="server-administration.html#sqlmode_ansi_quotes"><code class="literal">ANSI_QUOTES</code></a> SQL mode is
        enabled, string literals can be quoted only within single
        quotation marks because a string quoted within double quotation
        marks is interpreted as an identifier.
      </p><p>
        A <span class="firstterm">binary string</span> is a
        string of bytes. Every binary string has a character set and
        collation named <code class="literal">binary</code>. A
        <span class="firstterm">nonbinary string</span> is a
        string of characters. It has a character set other than
        <code class="literal">binary</code> and a collation that is compatible
        with the character set.
      </p><p>
        For both types of strings, comparisons are based on the numeric
        values of the string unit. For binary strings, the unit is the
        byte; comparisons use numeric byte values. For nonbinary
        strings, the unit is the character and some character sets
        support multibyte characters; comparisons use numeric character
        code values. Character code ordering is a function of the string
        collation. (For more information, see
        <a class="xref" href="charset.html#charset-binary-collations" title="10.8.5 The binary Collation Compared to _bin Collations">Section 10.8.5, “The binary Collation Compared to _bin Collations”</a>.)
      </p><p>
        A character string literal may have an optional character set
        introducer and <code class="literal">COLLATE</code> clause, to designate
        it as a string that uses a particular character set and
        collation:
      </p><a class="indexterm" name="idm140091700565072"></a><a class="indexterm" name="idm140091700563584"></a><pre data-lang="sql" class="programlisting">
[_<em class="replaceable"><code>charset_name</code></em>]'<em class="replaceable"><code>string</code></em>' [COLLATE <em class="replaceable"><code>collation_name</code></em>]
</pre><p>
        Examples:
      </p><pre data-lang="sql" class="programlisting">
SELECT _latin1'<em class="replaceable"><code>string</code></em>';
SELECT _binary'<em class="replaceable"><code>string</code></em>';
SELECT _utf8'<em class="replaceable"><code>string</code></em>' COLLATE utf8_danish_ci;
</pre><p>
        You can use
        <code class="literal">N'<em class="replaceable"><code>literal</code></em>'</code> (or
        <code class="literal">n'<em class="replaceable"><code>literal</code></em>'</code>) to
        create a string in the national character set. These statements
        are equivalent:
      </p><pre data-lang="sql" class="programlisting">
SELECT N'some text';
SELECT n'some text';
SELECT _utf8'some text';
</pre><p>
        For information about these forms of string syntax, see
        <a class="xref" href="charset.html#charset-national" title="10.3.7 The National Character Set">Section 10.3.7, “The National Character Set”</a>, and
        <a class="xref" href="charset.html#charset-introducer" title="10.3.8 Character Set Introducers">Section 10.3.8, “Character Set Introducers”</a>.
      </p><p>
        Within a string, certain sequences have special meaning unless
        the <a class="link" href="server-administration.html#sqlmode_no_backslash_escapes"><code class="literal">NO_BACKSLASH_ESCAPES</code></a> SQL
        mode is enabled. Each of these sequences begins with a backslash
        (<code class="literal">\</code>), known as the <span class="emphasis"><em>escape
        character</em></span>. MySQL recognizes the escape sequences
        shown in <a class="xref" href="language-structure.html#character-escape-sequences" title="Table 9.1 Special Character Escape Sequences">Table 9.1, “Special Character Escape Sequences”</a>. For all
        other escape sequences, backslash is ignored. That is, the
        escaped character is interpreted as if it was not escaped. For
        example, <code class="literal">\x</code> is just <code class="literal">x</code>.
        These sequences are case-sensitive. For example,
        <code class="literal">\b</code> is interpreted as a backspace, but
        <code class="literal">\B</code> is interpreted as <code class="literal">B</code>.
        Escape processing is done according to the character set
        indicated by the
        <a class="link" href="server-administration.html#sysvar_character_set_connection"><code class="literal">character_set_connection</code></a> system
        variable. This is true even for strings that are preceded by an
        introducer that indicates a different character set, as
        discussed in <a class="xref" href="charset.html#charset-literal" title="10.3.6 Character String Literal Character Set and Collation">Section 10.3.6, “Character String Literal Character Set and Collation”</a>.
</p>
<div class="table">
<a name="character-escape-sequences"></a><p class="title"><b>Table 9.1 Special Character Escape Sequences</b></p>
<div class="table-contents">
<table summary="Escape sequences and the characters they represent."><col width="15%"><col width="85%"><thead><tr>
            <th scope="col">Escape Sequence</th>
            <th scope="col">Character Represented by Sequence</th>
          </tr></thead><tbody><tr>
            <td scope="row"><code class="literal">\0</code><a class="indexterm" name="idm140091700533920"></a><a class="indexterm" name="idm140091700532976"></a></td>
            <td>An ASCII NUL (<code class="literal">X'00'</code>) character</td>
          </tr><tr>
            <td scope="row"><code class="literal">\'</code><a class="indexterm" name="idm140091700528560"></a><a class="indexterm" name="idm140091700527616"></a></td>
            <td>A single quote (<code class="literal">'</code>) character</td>
          </tr><tr>
            <td scope="row"><code class="literal">\"</code><a class="indexterm" name="idm140091700523200"></a><a class="indexterm" name="idm140091700522256"></a></td>
            <td>A double quote (<code class="literal">"</code>) character</td>
          </tr><tr>
            <td scope="row"><code class="literal">\b</code><a class="indexterm" name="idm140091700517840"></a><a class="indexterm" name="idm140091700516896"></a></td>
            <td>A backspace character</td>
          </tr><tr>
            <td scope="row"><code class="literal">\n</code><a class="indexterm" name="idm140091700513168"></a><a class="indexterm" name="idm140091700512224"></a><a class="indexterm" name="idm140091700511280"></a><a class="indexterm" name="idm140091700510336"></a></td>
            <td>A newline (linefeed) character</td>
          </tr><tr>
            <td scope="row"><code class="literal">\r</code><a class="indexterm" name="idm140091700506592"></a><a class="indexterm" name="idm140091700505648"></a><a class="indexterm" name="idm140091700504704"></a></td>
            <td>A carriage return character</td>
          </tr><tr>
            <td scope="row"><code class="literal">\t</code><a class="indexterm" name="idm140091700500960"></a><a class="indexterm" name="idm140091700500016"></a></td>
            <td>A tab character</td>
          </tr><tr>
            <td scope="row"><code class="literal">\Z</code><a class="indexterm" name="idm140091700496240"></a><a class="indexterm" name="idm140091700495296"></a></td>
            <td>ASCII 26 (Control+Z); see note following the table</td>
          </tr><tr>
            <td scope="row"><code class="literal">\\</code><a class="indexterm" name="idm140091700491536"></a><a class="indexterm" name="idm140091700490592"></a></td>
            <td>A backslash (<code class="literal">\</code>) character</td>
          </tr><tr>
            <td scope="row"><code class="literal">\%</code><a class="indexterm" name="idm140091700486128"></a><a class="indexterm" name="idm140091700485184"></a></td>
            <td>A <code class="literal">%</code> character; see note following the table</td>
          </tr><tr>
            <td scope="row"><code class="literal">\_</code><a class="indexterm" name="idm140091700480720"></a><a class="indexterm" name="idm140091700479776"></a></td>
            <td>A <code class="literal">_</code> character; see note following the table</td>
</tr></tbody></table>
</div>

</div>
<br class="table-break"><p>
        The ASCII 26 character can be encoded as <code class="literal">\Z</code>
        to enable you to work around the problem that ASCII 26 stands
        for END-OF-FILE on Windows. ASCII 26 within a file causes
        problems if you try to use <code class="literal">mysql
        <em class="replaceable"><code>db_name</code></em> &lt;
        <em class="replaceable"><code>file_name</code></em></code>.
      </p><p>
        The <code class="literal">\%</code> and <code class="literal">\_</code> sequences
        are used to search for literal instances of <code class="literal">%</code>
        and <code class="literal">_</code> in pattern-matching contexts where they
        would otherwise be interpreted as wildcard characters. See the
        description of the <a class="link" href="functions.html#operator_like"><code class="literal">LIKE</code></a> operator in
        <a class="xref" href="functions.html#string-comparison-functions" title="12.5.1 String Comparison Functions">Section 12.5.1, “String Comparison Functions”</a>. If you use
        <code class="literal">\%</code> or <code class="literal">\_</code> outside of
        pattern-matching contexts, they evaluate to the strings
        <code class="literal">\%</code> and <code class="literal">\_</code>, not to
        <code class="literal">%</code> and <code class="literal">_</code>.
      </p><a class="indexterm" name="idm140091700464448"></a><p>
        There are several ways to include quote characters within a
        string:
</p>
<div class="itemizedlist">
<ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>
            A <code class="literal">'</code> inside a string quoted with
            <code class="literal">'</code> may be written as
            <code class="literal">''</code>.
          </p></li><li class="listitem"><p>
            A <code class="literal">"</code> inside a string quoted with
            <code class="literal">"</code> may be written as
            <code class="literal">""</code>.
          </p></li><li class="listitem"><p>
            Precede the quote character by an escape character
            (<code class="literal">\</code>).
          </p></li><li class="listitem"><p>
            A <code class="literal">'</code> inside a string quoted with
            <code class="literal">"</code> needs no special treatment and need not
            be doubled or escaped. In the same way, <code class="literal">"</code>
            inside a string quoted with <code class="literal">'</code> needs no
            special treatment.
</p></li></ul>
</div>
<p>
        The following <a class="link" href="sql-syntax.html#select" title="13.2.10 SELECT Syntax"><code class="literal">SELECT</code></a> statements
        demonstrate how quoting and escaping work:
      </p><pre data-lang="sql" class="programlisting">
mysql&gt; <strong class="userinput"><code>SELECT 'hello', '"hello"', '""hello""', 'hel''lo', '\'hello';</code></strong>
+-------+---------+-----------+--------+--------+
| hello | "hello" | ""hello"" | hel'lo | 'hello |
+-------+---------+-----------+--------+--------+

mysql&gt; <strong class="userinput"><code>SELECT "hello", "'hello'", "''hello''", "hel""lo", "\"hello";</code></strong>
+-------+---------+-----------+--------+--------+
| hello | 'hello' | ''hello'' | hel"lo | "hello |
+-------+---------+-----------+--------+--------+

mysql&gt; <strong class="userinput"><code>SELECT 'This\nIs\nFour\nLines';</code></strong>
+--------------------+
| This
Is
Four
Lines |
+--------------------+

mysql&gt; <strong class="userinput"><code>SELECT 'disappearing\ backslash';</code></strong>
+------------------------+
| disappearing backslash |
+------------------------+
</pre><a class="indexterm" name="idm140091700444576"></a><p>
        To insert binary data into a string column (such as a
        <a class="link" href="data-types.html#blob" title="11.4.3 The BLOB and TEXT Types"><code class="literal">BLOB</code></a> column), you should
        represent certain characters by escape sequences. Backslash
        (<code class="literal">\</code>) and the quote character used to quote the
        string must be escaped. In certain client environments, it may
        also be necessary to escape <code class="literal">NUL</code> or Control+Z.
        The <a class="link" href="programs.html#mysql" title="4.5.1 mysql — The MySQL Command-Line Client"><span class="command"><strong>mysql</strong></span></a> client truncates quoted strings
        containing <code class="literal">NUL</code> characters if they are not
        escaped, and Control+Z may be taken for END-OF-FILE on Windows
        if not escaped. For the escape sequences that represent each of
        these characters, see
        <a class="xref" href="language-structure.html#character-escape-sequences" title="Table 9.1 Special Character Escape Sequences">Table 9.1, “Special Character Escape Sequences”</a>.
      </p><a class="indexterm" name="idm140091700437312"></a><a class="indexterm" name="idm140091700436240"></a><a class="indexterm" name="idm140091700434752"></a><a class="indexterm" name="idm140091700433712"></a><a class="indexterm" name="idm140091700432640"></a><p>
        When writing application programs, any string that might contain
        any of these special characters must be properly escaped before
        the string is used as a data value in an SQL statement that is
        sent to the MySQL server. You can do this in two ways:
</p>
<div class="itemizedlist">
<ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>
            Process the string with a function that escapes the special
            characters. In a C program, you can use the
            <a class="link" href="connectors-apis.html#mysql-real-escape-string-quote" title="28.7.7.56 mysql_real_escape_string_quote()"><code class="literal">mysql_real_escape_string_quote()</code></a>
            C API function to escape characters. See
            <a class="xref" href="connectors-apis.html#mysql-real-escape-string-quote" title="28.7.7.56 mysql_real_escape_string_quote()">Section 28.7.7.56, “mysql_real_escape_string_quote()”</a>. Within SQL
            statements that construct other SQL statements, you can use
            the <a class="link" href="functions.html#function_quote"><code class="literal">QUOTE()</code></a> function. The
            Perl DBI interface provides a <code class="literal">quote</code>
            method to convert special characters to the proper escape
            sequences. See <a class="xref" href="connectors-apis.html#apis-perl" title="28.9 MySQL Perl API">Section 28.9, “MySQL Perl API”</a>. Other language
            interfaces may provide a similar capability.
          </p></li><li class="listitem"><p>
            As an alternative to explicitly escaping special characters,
            many MySQL APIs provide a placeholder capability that
            enables you to insert special markers into a statement
            string, and then bind data values to them when you issue the
            statement. In this case, the API takes care of escaping
            special characters in the values for you.
</p></li></ul>
</div>

</div>
<div class="section">
<div class="titlepage">
<div>
<div>
<h3 class="title"><a name="number-literals"></a>9.1.2 Numeric Literals</h3>

</div>

</div>

</div>
<a class="indexterm" name="idm140091700421984"></a><a class="indexterm" name="idm140091700420896"></a><a class="indexterm" name="idm140091700419808"></a><a class="indexterm" name="idm140091700418320"></a><a class="indexterm" name="idm140091700416832"></a><a class="indexterm" name="idm140091700415344"></a><a class="indexterm" name="idm140091700414272"></a><a class="indexterm" name="idm140091700412784"></a><a class="indexterm" name="idm140091700411712"></a><a class="indexterm" name="idm140091700410640"></a><a class="indexterm" name="idm140091700409568"></a><a class="indexterm" name="idm140091700408496"></a><p>
        Number literals include exact-value (integer and
        <a class="link" href="data-types.html#fixed-point-types" title="11.2.2 Fixed-Point Types (Exact Value) - DECIMAL, NUMERIC"><code class="literal">DECIMAL</code></a>) literals and
        approximate-value (floating-point) literals.
      </p><p>
        Integers are represented as a sequence of digits. Numbers may
        include <code class="literal">.</code> as a decimal separator. Numbers may
        be preceded by <code class="literal">-</code> or <code class="literal">+</code> to
        indicate a negative or positive value, respectively. Numbers
        represented in scientific notation with a mantissa and exponent
        are approximate-value numbers.
      </p><p>
        Exact-value numeric literals have an integer part or fractional
        part, or both. They may be signed. Examples:
        <code class="literal">1</code>, <code class="literal">.2</code>,
        <code class="literal">3.4</code>, <code class="literal">-5</code>,
        <code class="literal">-6.78</code>, <code class="literal">+9.10</code>.
      </p><p>
        Approximate-value numeric literals are represented in scientific
        notation with a mantissa and exponent. Either or both parts may
        be signed. Examples: <code class="literal">1.2E3</code>,
        <code class="literal">1.2E-3</code>, <code class="literal">-1.2E3</code>,
        <code class="literal">-1.2E-3</code>.
      </p><p>
        Two numbers that look similar may be treated differently. For
        example, <code class="literal">2.34</code> is an exact-value (fixed-point)
        number, whereas <code class="literal">2.34E0</code> is an
        approximate-value (floating-point) number.
      </p><p>
        The <a class="link" href="data-types.html#fixed-point-types" title="11.2.2 Fixed-Point Types (Exact Value) - DECIMAL, NUMERIC"><code class="literal">DECIMAL</code></a> data type is a
        fixed-point type and calculations are exact. In MySQL, the
        <a class="link" href="data-types.html#fixed-point-types" title="11.2.2 Fixed-Point Types (Exact Value) - DECIMAL, NUMERIC"><code class="literal">DECIMAL</code></a> type has several
        synonyms: <a class="link" href="data-types.html#fixed-point-types" title="11.2.2 Fixed-Point Types (Exact Value) - DECIMAL, NUMERIC"><code class="literal">NUMERIC</code></a>,
        <a class="link" href="data-types.html#fixed-point-types" title="11.2.2 Fixed-Point Types (Exact Value) - DECIMAL, NUMERIC"><code class="literal">DEC</code></a>,
        <a class="link" href="data-types.html#fixed-point-types" title="11.2.2 Fixed-Point Types (Exact Value) - DECIMAL, NUMERIC"><code class="literal">FIXED</code></a>. The integer types also are
        exact-value types. For more information about exact-value
        calculations, see <a class="xref" href="functions.html#precision-math" title="12.25 Precision Math">Section 12.25, “Precision Math”</a>.
      </p><p>
        The <a class="link" href="data-types.html#floating-point-types" title="11.2.3 Floating-Point Types (Approximate Value) - FLOAT, DOUBLE"><code class="literal">FLOAT</code></a> and
        <a class="link" href="data-types.html#floating-point-types" title="11.2.3 Floating-Point Types (Approximate Value) - FLOAT, DOUBLE"><code class="literal">DOUBLE</code></a> data types are
        floating-point types and calculations are approximate. In MySQL,
        types that are synonymous with
        <a class="link" href="data-types.html#floating-point-types" title="11.2.3 Floating-Point Types (Approximate Value) - FLOAT, DOUBLE"><code class="literal">FLOAT</code></a> or
        <a class="link" href="data-types.html#floating-point-types" title="11.2.3 Floating-Point Types (Approximate Value) - FLOAT, DOUBLE"><code class="literal">DOUBLE</code></a> are
        <a class="link" href="data-types.html#floating-point-types" title="11.2.3 Floating-Point Types (Approximate Value) - FLOAT, DOUBLE"><code class="literal">DOUBLE PRECISION</code></a> and
        <a class="link" href="data-types.html#floating-point-types" title="11.2.3 Floating-Point Types (Approximate Value) - FLOAT, DOUBLE"><code class="literal">REAL</code></a>.
      </p><p>
        An integer may be used in a floating-point context; it is
        interpreted as the equivalent floating-point number.
</p>
</div>
<div class="section">
<div class="titlepage">
<div>
<div>
<h3 class="title"><a name="date-and-time-literals"></a>9.1.3 Date and Time Literals</h3>

</div>

</div>

</div>
<a class="indexterm" name="idm140091700374464"></a><a class="indexterm" name="idm140091700372976"></a><a class="indexterm" name="idm140091700371488"></a><a class="indexterm" name="idm140091700370416"></a><p>
        Date and time values can be represented in several formats, such
        as quoted strings or as numbers, depending on the exact type of
        the value and other factors. For example, in contexts where
        MySQL expects a date, it interprets any of
        <code class="literal">'2015-07-21'</code>, <code class="literal">'20150721'</code>,
        and <code class="literal">20150721</code> as a date.
      </p><p>
        This section describes the acceptable formats for date and time
        literals. For more information about the temporal data types,
        such as the range of permitted values, consult these sections:
</p>
<div class="itemizedlist">
<ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>
            <a class="xref" href="data-types.html#date-and-time-type-overview" title="11.1.2 Date and Time Type Overview">Section 11.1.2, “Date and Time Type Overview”</a>
          </p></li><li class="listitem"><p>
            <a class="xref" href="data-types.html#date-and-time-types" title="11.3 Date and Time Types">Section 11.3, “Date and Time Types”</a>
</p></li></ul>
</div>
<p><a name="date-and-time-standard-sql-literals"></a><b>Standard SQL and ODBC Date and Time Literals. </b>
          Standard SQL permits temporal literals to be specified using a
          type keyword and a string. The space between the keyword and
          string is optional.
        </p><pre data-lang="sql" class="programlisting">
DATE '<em class="replaceable"><code>str</code></em>'
TIME '<em class="replaceable"><code>str</code></em>'
TIMESTAMP '<em class="replaceable"><code>str</code></em>'
</pre><p>
        MySQL recognizes those constructions and also the corresponding
        ODBC syntax:
      </p><pre data-lang="clike" class="programlisting">
{ d '<em class="replaceable"><code>str</code></em>' }
{ t '<em class="replaceable"><code>str</code></em>' }
{ ts '<em class="replaceable"><code>str</code></em>' }
</pre><p>
        MySQL uses the type keyword and these constructions produce
        <a class="link" href="data-types.html#datetime" title="11.3.1 The DATE, DATETIME, and TIMESTAMP Types"><code class="literal">DATE</code></a>,
        <a class="link" href="data-types.html#time" title="11.3.2 The TIME Type"><code class="literal">TIME</code></a>, and
        <a class="link" href="data-types.html#datetime" title="11.3.1 The DATE, DATETIME, and TIMESTAMP Types"><code class="literal">DATETIME</code></a> values, respectively,
        including a trailing fractional seconds part if specified. The
        <a class="link" href="data-types.html#datetime" title="11.3.1 The DATE, DATETIME, and TIMESTAMP Types"><code class="literal">TIMESTAMP</code></a> syntax produces a
        <a class="link" href="data-types.html#datetime" title="11.3.1 The DATE, DATETIME, and TIMESTAMP Types"><code class="literal">DATETIME</code></a> value in MySQL because
        <a class="link" href="data-types.html#datetime" title="11.3.1 The DATE, DATETIME, and TIMESTAMP Types"><code class="literal">DATETIME</code></a> has a range that more
        closely corresponds to the standard SQL
        <a class="link" href="data-types.html#datetime" title="11.3.1 The DATE, DATETIME, and TIMESTAMP Types"><code class="literal">TIMESTAMP</code></a> type, which has a year
        range from <code class="literal">0001</code> to <code class="literal">9999</code>.
        (The MySQL <a class="link" href="data-types.html#datetime" title="11.3.1 The DATE, DATETIME, and TIMESTAMP Types"><code class="literal">TIMESTAMP</code></a> year range
        is <code class="literal">1970</code> to <code class="literal">2038</code>.)
      </p><p><a name="date-and-time-string-numeric-literals"></a><b>String and Numeric Literals in Date and Time Context. </b>
          MySQL recognizes <a class="link" href="data-types.html#datetime" title="11.3.1 The DATE, DATETIME, and TIMESTAMP Types"><code class="literal">DATE</code></a> values in
          these formats:
</p>
<div class="itemizedlist">
<ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>
            As a string in either
            <em class="replaceable"><code>'YYYY-MM-DD'</code></em> or
            <em class="replaceable"><code>'YY-MM-DD'</code></em> format. A
            <span class="quote">“<span class="quote">relaxed</span>”</span> syntax is permitted: Any punctuation
            character may be used as the delimiter between date parts.
            For example, <code class="literal">'2012-12-31'</code>,
            <code class="literal">'2012/12/31'</code>,
            <code class="literal">'2012^12^31'</code>, and
            <code class="literal">'2012@12@31'</code> are equivalent.
          </p></li><li class="listitem"><p>
            As a string with no delimiters in either
            <em class="replaceable"><code>'YYYYMMDD'</code></em> or
            <em class="replaceable"><code>'YYMMDD'</code></em> format, provided that
            the string makes sense as a date. For example,
            <code class="literal">'20070523'</code> and
            <code class="literal">'070523'</code> are interpreted as
            <code class="literal">'2007-05-23'</code>, but
            <code class="literal">'071332'</code> is illegal (it has nonsensical
            month and day parts) and becomes
            <code class="literal">'0000-00-00'</code>.
          </p></li><li class="listitem"><p>
            As a number in either <em class="replaceable"><code>YYYYMMDD</code></em> or
            <em class="replaceable"><code>YYMMDD</code></em> format, provided that the
            number makes sense as a date. For example,
            <code class="literal">19830905</code> and <code class="literal">830905</code>
            are interpreted as <code class="literal">'1983-09-05'</code>.
</p></li></ul>
</div>
<p>
        MySQL recognizes <a class="link" href="data-types.html#datetime" title="11.3.1 The DATE, DATETIME, and TIMESTAMP Types"><code class="literal">DATETIME</code></a> and
        <a class="link" href="data-types.html#datetime" title="11.3.1 The DATE, DATETIME, and TIMESTAMP Types"><code class="literal">TIMESTAMP</code></a> values in these
        formats:
</p>
<div class="itemizedlist">
<ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>
            As a string in either <em class="replaceable"><code>'YYYY-MM-DD
            hh:mm:ss'</code></em> or <em class="replaceable"><code>'YY-MM-DD
            hh:mm:ss'</code></em> format. A <span class="quote">“<span class="quote">relaxed</span>”</span>
            syntax is permitted here, too: Any punctuation character may
            be used as the delimiter between date parts or time parts.
            For example, <code class="literal">'2012-12-31 11:30:45'</code>,
            <code class="literal">'2012^12^31 11+30+45'</code>,
            <code class="literal">'2012/12/31 11*30*45'</code>, and
            <code class="literal">'2012@12@31 11^30^45'</code> are equivalent.
          </p><p>
            The only delimiter recognized between a date and time part
            and a fractional seconds part is the decimal point.
          </p><p>
            The date and time parts can be separated by
            <code class="literal">T</code> rather than a space. For example,
            <code class="literal">'2012-12-31 11:30:45'</code>
            <code class="literal">'2012-12-31T11:30:45'</code> are equivalent.
          </p></li><li class="listitem"><p>
            As a string with no delimiters in either
            <em class="replaceable"><code>'YYYYMMDDhhmmss'</code></em> or
            <em class="replaceable"><code>'YYMMDDhhmmss'</code></em> format, provided
            that the string makes sense as a date. For example,
            <code class="literal">'20070523091528'</code> and
            <code class="literal">'070523091528'</code> are interpreted as
            <code class="literal">'2007-05-23 09:15:28'</code>, but
            <code class="literal">'071122129015'</code> is illegal (it has a
            nonsensical minute part) and becomes <code class="literal">'0000-00-00
            00:00:00'</code>.
          </p></li><li class="listitem"><p>
            As a number in either
            <em class="replaceable"><code>YYYYMMDDhhmmss</code></em> or
            <em class="replaceable"><code>YYMMDDhhmmss</code></em> format, provided
            that the number makes sense as a date. For example,
            <code class="literal">19830905132800</code> and
            <code class="literal">830905132800</code> are interpreted as
            <code class="literal">'1983-09-05 13:28:00'</code>.
</p></li></ul>
</div>
<p>
        A <a class="link" href="data-types.html#datetime" title="11.3.1 The DATE, DATETIME, and TIMESTAMP Types"><code class="literal">DATETIME</code></a> or
        <a class="link" href="data-types.html#datetime" title="11.3.1 The DATE, DATETIME, and TIMESTAMP Types"><code class="literal">TIMESTAMP</code></a> value can include a
        trailing fractional seconds part in up to microseconds (6
        digits) precision. The fractional part should always be
        separated from the rest of the time by a decimal point; no other
        fractional seconds delimiter is recognized. For information
        about fractional seconds support in MySQL, see
        <a class="xref" href="data-types.html#fractional-seconds" title="11.3.5 Fractional Seconds in Time Values">Section 11.3.5, “Fractional Seconds in Time Values”</a>.
      </p><p>
        Dates containing two-digit year values are ambiguous because the
        century is unknown. MySQL interprets two-digit year values using
        these rules:
</p>
<div class="itemizedlist">
<ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>
            Year values in the range <code class="literal">70-99</code> are
            converted to <code class="literal">1970-1999</code>.
          </p></li><li class="listitem"><p>
            Year values in the range <code class="literal">00-69</code> are
            converted to <code class="literal">2000-2069</code>.
</p></li></ul>
</div>
<p>
        See also <a class="xref" href="data-types.html#two-digit-years" title="11.3.7 Two-Digit Years in Dates">Section 11.3.7, “Two-Digit Years in Dates”</a>.
      </p><p>
        For values specified as strings that include date part
        delimiters, it is unnecessary to specify two digits for month or
        day values that are less than <code class="literal">10</code>.
        <code class="literal">'2015-6-9'</code> is the same as
        <code class="literal">'2015-06-09'</code>. Similarly, for values specified
        as strings that include time part delimiters, it is unnecessary
        to specify two digits for hour, minute, or second values that
        are less than <code class="literal">10</code>. <code class="literal">'2015-10-30
        1:2:3'</code> is the same as <code class="literal">'2015-10-30
        01:02:03'</code>.
      </p><p>
        Values specified as numbers should be 6, 8, 12, or 14 digits
        long. If a number is 8 or 14 digits long, it is assumed to be in
        <em class="replaceable"><code>YYYYMMDD</code></em> or
        <em class="replaceable"><code>YYYYMMDDhhmmss</code></em> format and that the
        year is given by the first 4 digits. If the number is 6 or 12
        digits long, it is assumed to be in
        <em class="replaceable"><code>YYMMDD</code></em> or
        <em class="replaceable"><code>YYMMDDhhmmss</code></em> format and that the year
        is given by the first 2 digits. Numbers that are not one of
        these lengths are interpreted as though padded with leading
        zeros to the closest length.
      </p><a class="indexterm" name="idm140091700284512"></a><a class="indexterm" name="idm140091700283440"></a><p>
        Values specified as nondelimited strings are interpreted
        according their length. For a string 8 or 14 characters long,
        the year is assumed to be given by the first 4 characters.
        Otherwise, the year is assumed to be given by the first 2
        characters. The string is interpreted from left to right to find
        year, month, day, hour, minute, and second values, for as many
        parts as are present in the string. This means you should not
        use strings that have fewer than 6 characters. For example, if
        you specify <code class="literal">'9903'</code>, thinking that represents
        March, 1999, MySQL converts it to the <span class="quote">“<span class="quote">zero</span>”</span> date
        value. This occurs because the year and month values are
        <code class="literal">99</code> and <code class="literal">03</code>, but the day
        part is completely missing. However, you can explicitly specify
        a value of zero to represent missing month or day parts. For
        example, to insert the value <code class="literal">'1999-03-00'</code>,
        use <code class="literal">'990300'</code>.
      </p><p>
        MySQL recognizes <a class="link" href="data-types.html#time" title="11.3.2 The TIME Type"><code class="literal">TIME</code></a> values in
        these formats:
</p>
<div class="itemizedlist">
<ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>
            As a string in <em class="replaceable"><code>'D hh:mm:ss'</code></em>
            format. You can also use one of the following
            <span class="quote">“<span class="quote">relaxed</span>”</span> syntaxes:
            <em class="replaceable"><code>'hh:mm:ss'</code></em>,
            <em class="replaceable"><code>'hh:mm'</code></em>, <em class="replaceable"><code>'D
            hh:mm'</code></em>, <em class="replaceable"><code>'D hh'</code></em>, or
            <em class="replaceable"><code>'ss'</code></em>. Here
            <em class="replaceable"><code>D</code></em> represents days and can have a
            value from 0 to 34.
          </p></li><li class="listitem"><p>
            As a string with no delimiters in
            <em class="replaceable"><code>'hhmmss'</code></em> format, provided that it
            makes sense as a time. For example,
            <code class="literal">'101112'</code> is understood as
            <code class="literal">'10:11:12'</code>, but
            <code class="literal">'109712'</code> is illegal (it has a nonsensical
            minute part) and becomes <code class="literal">'00:00:00'</code>.
          </p></li><li class="listitem"><p>
            As a number in <em class="replaceable"><code>hhmmss</code></em> format,
            provided that it makes sense as a time. For example,
            <code class="literal">101112</code> is understood as
            <code class="literal">'10:11:12'</code>. The following alternative
            formats are also understood: <em class="replaceable"><code>ss</code></em>,
            <em class="replaceable"><code>mmss</code></em>, or
            <em class="replaceable"><code>hhmmss</code></em>.
</p></li></ul>
</div>
<p>
        A trailing fractional seconds part is recognized in the
        <em class="replaceable"><code>'D hh:mm:ss.fraction'</code></em>,
        <em class="replaceable"><code>'hh:mm:ss.fraction'</code></em>,
        <em class="replaceable"><code>'hhmmss.fraction'</code></em>, and
        <em class="replaceable"><code>hhmmss.fraction</code></em> time formats, where
        <code class="literal">fraction</code> is the fractional part in up to
        microseconds (6 digits) precision. The fractional part should
        always be separated from the rest of the time by a decimal
        point; no other fractional seconds delimiter is recognized. For
        information about fractional seconds support in MySQL, see
        <a class="xref" href="data-types.html#fractional-seconds" title="11.3.5 Fractional Seconds in Time Values">Section 11.3.5, “Fractional Seconds in Time Values”</a>.
      </p><p>
        For <a class="link" href="data-types.html#time" title="11.3.2 The TIME Type"><code class="literal">TIME</code></a> values specified as
        strings that include a time part delimiter, it is unnecessary to
        specify two digits for hours, minutes, or seconds values that
        are less than <code class="literal">10</code>. <code class="literal">'8:3:2'</code>
        is the same as <code class="literal">'08:03:02'</code>.
</p>
</div>
<div class="section">
<div class="titlepage">
<div>
<div>
<h3 class="title"><a name="hexadecimal-literals"></a>9.1.4 Hexadecimal Literals</h3>

</div>

</div>

</div>
<a class="indexterm" name="idm140091700252464"></a><a class="indexterm" name="idm140091700250976"></a><p>
        Hexadecimal literal values are written using
        <code class="literal">X'<em class="replaceable"><code>val</code></em>'</code> or
        <code class="literal">0x<em class="replaceable"><code>val</code></em></code> notation,
        where <em class="replaceable"><code>val</code></em> contains hexadecimal digits
        (<code class="literal">0..9</code>, <code class="literal">A..F</code>). Lettercase
        of the digits and of any leading <code class="literal">X</code> does not
        matter. A leading <code class="literal">0x</code> is case-sensitive and
        cannot be written as <code class="literal">0X</code>.
      </p><p>
        Legal hexadecimal literals:
      </p><pre data-lang="sql" class="programlisting">
X'01AF'
X'01af'
x'01AF'
x'01af'
0x01AF
0x01af
</pre><p>
        Illegal hexadecimal literals:
      </p><pre data-lang="none" class="programlisting">
X'0G'   (G is not a hexadecimal digit)
0X01AF  (0X must be written as 0x)
</pre><p>
        Values written using
        <code class="literal">X'<em class="replaceable"><code>val</code></em>'</code> notation
        must contain an even number of digits or a syntax error occurs.
        To correct the problem, pad the value with a leading zero:
      </p><pre data-lang="sql" class="programlisting">
mysql&gt; <strong class="userinput"><code>SET @s = X'FFF';</code></strong>
ERROR 1064 (42000): You have an error in your SQL syntax;
check the manual that corresponds to your MySQL server
version for the right syntax to use near 'X'FFF''

mysql&gt; <strong class="userinput"><code>SET @s = X'0FFF';</code></strong>
Query OK, 0 rows affected (0.00 sec)
</pre><p>
        Values written using
        <code class="literal">0x<em class="replaceable"><code>val</code></em></code> notation
        that contain an odd number of digits are treated as having an
        extra leading <code class="literal">0</code>. For example,
        <code class="literal">0xaaa</code> is interpreted as
        <code class="literal">0x0aaa</code>.
      </p><p>
        By default, a hexadecimal literal is a binary string, where each
        pair of hexadecimal digits represents a character:
      </p><pre data-lang="sql" class="programlisting">
mysql&gt; <strong class="userinput"><code>SELECT X'4D7953514C', CHARSET(X'4D7953514C');</code></strong>
+---------------+------------------------+
| X'4D7953514C' | CHARSET(X'4D7953514C') |
+---------------+------------------------+
| MySQL         | binary                 |
+---------------+------------------------+
mysql&gt; <strong class="userinput"><code>SELECT 0x5461626c65, CHARSET(0x5461626c65);</code></strong>
+--------------+-----------------------+
| 0x5461626c65 | CHARSET(0x5461626c65) |
+--------------+-----------------------+
| Table        | binary                |
+--------------+-----------------------+
</pre><p>
        A hexadecimal literal may have an optional character set
        introducer and <code class="literal">COLLATE</code> clause, to designate
        it as a string that uses a particular character set and
        collation:
      </p><a class="indexterm" name="idm140091700227584"></a><a class="indexterm" name="idm140091700226096"></a><pre data-lang="sql" class="programlisting">
[_<em class="replaceable"><code>charset_name</code></em>] X'<em class="replaceable"><code>val</code></em>' [COLLATE <em class="replaceable"><code>collation_name</code></em>]
</pre><p>
        Examples:
      </p><pre data-lang="sql" class="programlisting">
SELECT _latin1 X'4D7953514C';
SELECT _utf8 0x4D7953514C COLLATE utf8_danish_ci;
</pre><p>
        The examples use
        <code class="literal">X'<em class="replaceable"><code>val</code></em>'</code> notation,
        but <code class="literal">0x<em class="replaceable"><code>val</code></em></code> notation
        permits introducers as well. For information about introducers,
        see <a class="xref" href="charset.html#charset-introducer" title="10.3.8 Character Set Introducers">Section 10.3.8, “Character Set Introducers”</a>.
      </p><p>
        In numeric contexts, MySQL treats a hexadecimal literal like a
        <a class="link" href="data-types.html#integer-types" title="11.2.1 Integer Types (Exact Value) - INTEGER, INT, SMALLINT, TINYINT, MEDIUMINT, BIGINT"><code class="literal">BIGINT</code></a> (64-bit integer). To
        ensure numeric treatment of a hexadecimal literal, use it in
        numeric context. Ways to do this include adding 0 or using
        <a class="link" href="functions.html#function_cast"><code class="literal">CAST(... AS UNSIGNED)</code></a>. For
        example, a hexadecimal literal assigned to a user-defined
        variable is a binary string by default. To assign the value as a
        number, use it in numeric context:
      </p><pre data-lang="sql" class="programlisting">
mysql&gt; <strong class="userinput"><code>SET @v1 = X'41';</code></strong>
mysql&gt; <strong class="userinput"><code>SET @v2 = X'41'+0;</code></strong>
mysql&gt; <strong class="userinput"><code>SET @v3 = CAST(X'41' AS UNSIGNED);</code></strong>
mysql&gt; <strong class="userinput"><code>SELECT @v1, @v2, @v3;</code></strong>
+------+------+------+
| @v1  | @v2  | @v3  |
+------+------+------+
| A    |   65 |   65 |
+------+------+------+
</pre><p>
        An empty hexadecimal value (<code class="literal">X''</code>) evaluates to
        a zero-length binary string. Converted to a number, it produces
        0:
      </p><pre data-lang="sql" class="programlisting">
mysql&gt; <strong class="userinput"><code>SELECT CHARSET(X''), LENGTH(X'');</code></strong>
+--------------+-------------+
| CHARSET(X'') | LENGTH(X'') |
+--------------+-------------+
| binary       |           0 |
+--------------+-------------+
mysql&gt; <strong class="userinput"><code>SELECT X''+0;</code></strong>
+-------+
| X''+0 |
+-------+
|     0 |
+-------+
</pre><p>
        The <code class="literal">X'<em class="replaceable"><code>val</code></em>'</code>
        notation is based on standard SQL. The <code class="literal">0x</code>
        notation is based on ODBC, for which hexadecimal strings are
        often used to supply values for
        <a class="link" href="data-types.html#blob" title="11.4.3 The BLOB and TEXT Types"><code class="literal">BLOB</code></a> columns.
      </p><p>
        To convert a string or a number to a string in hexadecimal
        format, use the <a class="link" href="functions.html#function_hex"><code class="literal">HEX()</code></a> function:
      </p><pre data-lang="sql" class="programlisting">
mysql&gt; <strong class="userinput"><code>SELECT HEX('cat');</code></strong>
+------------+
| HEX('cat') |
+------------+
| 636174     |
+------------+
mysql&gt; <strong class="userinput"><code>SELECT X'636174';</code></strong>
+-----------+
| X'636174' |
+-----------+
| cat       |
+-----------+
</pre><a class="indexterm" name="idm140091700199216"></a><a class="indexterm" name="idm140091700197728"></a><p>
        For hexadecimal literals, bit operations are considered numeric
        context, but bit operations permit numeric or binary string
        arguments in MySQL 8.0 and higher. To explicitly
        specify binary string context for hexadecimal literals, use a
        <code class="literal">_binary</code> introducer for at least one of the
        arguments:
      </p><pre data-lang="sql" class="programlisting">
mysql&gt; <strong class="userinput"><code>SET @v1 = X'000D' | X'0BC0';</code></strong>
mysql&gt; <strong class="userinput"><code>SET @v2 = _binary X'000D' | X'0BC0';</code></strong>
mysql&gt; <strong class="userinput"><code>SELECT HEX(@v1), HEX(@v2);</code></strong>
+----------+----------+
| HEX(@v1) | HEX(@v2) |
+----------+----------+
| BCD      | 0BCD     |
+----------+----------+
</pre><p>
        The displayed result appears similar for both bit operations,
        but the result without <code class="literal">_binary</code> is a
        <code class="literal">BIGINT</code> value, whereas the result with
        <code class="literal">_binary</code> is a binary string. Due to the
        difference in result types, the displayed values differ:
        High-order 0 digits are not displayed for the numeric result.
</p>
</div>
<div class="section">
<div class="titlepage">
<div>
<div>
<h3 class="title"><a name="bit-value-literals"></a>9.1.5 Bit-Value Literals</h3>

</div>

</div>

</div>
<a class="indexterm" name="idm140091700187424"></a><a class="indexterm" name="idm140091700185936"></a><p>
        Bit-value literals are written using
        <code class="literal">b'<em class="replaceable"><code>val</code></em>'</code> or
        <code class="literal">0b<em class="replaceable"><code>val</code></em></code> notation.
        <em class="replaceable"><code>val</code></em> is a binary value written using
        zeros and ones. Lettercase of any leading <code class="literal">b</code>
        does not matter. A leading <code class="literal">0b</code> is case
        sensitive and cannot be written as <code class="literal">0B</code>.
      </p><p>
        Legal bit-value literals:
      </p><pre data-lang="sql" class="programlisting">
b'01'
B'01'
0b01
</pre><p>
        Illegal bit-value literals:
      </p><pre data-lang="none" class="programlisting">
b'2'    (2 is not a binary digit)
0B01    (0B must be written as 0b)
</pre><p>
        By default, a bit-value literal is a binary string:
      </p><pre data-lang="sql" class="programlisting">
mysql&gt; <strong class="userinput"><code>SELECT b'1000001', CHARSET(b'1000001');</code></strong>
+------------+---------------------+
| b'1000001' | CHARSET(b'1000001') |
+------------+---------------------+
| A          | binary              |
+------------+---------------------+
mysql&gt; <strong class="userinput"><code>SELECT 0b1100001, CHARSET(0b1100001);</code></strong>
+-----------+--------------------+
| 0b1100001 | CHARSET(0b1100001) |
+-----------+--------------------+
| a         | binary             |
+-----------+--------------------+
</pre><p>
        A bit-value literal may have an optional character set
        introducer and <code class="literal">COLLATE</code> clause, to designate
        it as a string that uses a particular character set and
        collation:
      </p><a class="indexterm" name="idm140091700172144"></a><a class="indexterm" name="idm140091700170656"></a><pre data-lang="sql" class="programlisting">
[_<em class="replaceable"><code>charset_name</code></em>] b'<em class="replaceable"><code>val</code></em>' [COLLATE <em class="replaceable"><code>collation_name</code></em>]
</pre><p>
        Examples:
      </p><pre data-lang="sql" class="programlisting">
SELECT _latin1 b'1000001';
SELECT _utf8 0b1000001 COLLATE utf8_danish_ci;
</pre><p>
        The examples use
        <code class="literal">b'<em class="replaceable"><code>val</code></em>'</code> notation,
        but <code class="literal">0b<em class="replaceable"><code>val</code></em></code> notation
        permits introducers as well. For information about introducers,
        see <a class="xref" href="charset.html#charset-introducer" title="10.3.8 Character Set Introducers">Section 10.3.8, “Character Set Introducers”</a>.
      </p><p>
        In numeric contexts, MySQL treats a bit literal like an integer.
        To ensure numeric treatment of a bit literal, use it in numeric
        context. Ways to do this include adding 0 or using
        <a class="link" href="functions.html#function_cast"><code class="literal">CAST(... AS UNSIGNED)</code></a>. For
        example, a bit literal assigned to a user-defined variable is a
        binary string by default. To assign the value as a number, use
        it in numeric context:
      </p><pre data-lang="sql" class="programlisting">
mysql&gt; <strong class="userinput"><code>SET @v1 = b'1100001';</code></strong>
mysql&gt; <strong class="userinput"><code>SET @v2 = b'1100001'+0;</code></strong>
mysql&gt; <strong class="userinput"><code>SET @v3 = CAST(b'1100001' AS UNSIGNED);</code></strong>
mysql&gt; <strong class="userinput"><code>SELECT @v1, @v2, @v3;</code></strong>
+------+------+------+
| @v1  | @v2  | @v3  |
+------+------+------+
| a    |   97 |   97 |
+------+------+------+
</pre><p>
        An empty bit value (<code class="literal">b''</code>) evaluates to a
        zero-length binary string. Converted to a number, it produces 0:
      </p><pre data-lang="sql" class="programlisting">
mysql&gt; <strong class="userinput"><code>SELECT CHARSET(b''), LENGTH(b'');</code></strong>
+--------------+-------------+
| CHARSET(b'') | LENGTH(b'') |
+--------------+-------------+
| binary       |           0 |
+--------------+-------------+
mysql&gt; <strong class="userinput"><code>SELECT b''+0;</code></strong>
+-------+
| b''+0 |
+-------+
|     0 |
+-------+
</pre><p>
        Bit-value notation is convenient for specifying values to be
        assigned to <a class="link" href="data-types.html#bit-type" title="11.2.4 Bit-Value Type - BIT"><code class="literal">BIT</code></a> columns:
      </p><pre data-lang="sql" class="programlisting">

mysql&gt; <strong class="userinput"><code>CREATE TABLE t (b BIT(8));</code></strong>
mysql&gt; <strong class="userinput"><code>INSERT INTO t SET b = b'11111111';</code></strong>
mysql&gt; <strong class="userinput"><code>INSERT INTO t SET b = b'1010';</code></strong>
mysql&gt; <strong class="userinput"><code>INSERT INTO t SET b = b'0101';</code></strong>
</pre><p>
        Bit values in result sets are returned as binary values, which
        may not display well. To convert a bit value to printable form,
        use it in numeric context or use a conversion function such as
        <a class="link" href="functions.html#function_bin"><code class="literal">BIN()</code></a> or
        <a class="link" href="functions.html#function_hex"><code class="literal">HEX()</code></a>. High-order 0 digits are
        not displayed in the converted value.
      </p><pre data-lang="sql" class="programlisting">
mysql&gt; <strong class="userinput"><code>SELECT b+0, BIN(b), OCT(b), HEX(b) FROM t;</code></strong>
+------+----------+--------+--------+
| b+0  | BIN(b)   | OCT(b) | HEX(b) |
+------+----------+--------+--------+
|  255 | 11111111 | 377    | FF     |
|   10 | 1010     | 12     | A      |
|    5 | 101      | 5      | 5      |
+------+----------+--------+--------+
</pre><a class="indexterm" name="idm140091700142176"></a><a class="indexterm" name="idm140091700140688"></a><p>
        For bit literals, bit operations are considered numeric context,
        but bit operations permit numeric or binary string arguments in
        MySQL 8.0 and higher. To explicitly specify binary
        string context for bit literals, use a
        <code class="literal">_binary</code> introducer for at least one of the
        arguments:
      </p><pre data-lang="sql" class="programlisting">
mysql&gt; <strong class="userinput"><code>SET @v1 = b'000010101' | b'000101010';</code></strong>
mysql&gt; <strong class="userinput"><code>SET @v2 = _binary b'000010101' | _binary b'000101010';</code></strong>
mysql&gt; <strong class="userinput"><code>SELECT HEX(@v1), HEX(@v2);</code></strong>
+----------+----------+
| HEX(@v1) | HEX(@v2) |
+----------+----------+
| 3F       | 003F     |
+----------+----------+
</pre><p>
        The displayed result appears similar for both bit operations,
        but the result without <code class="literal">_binary</code> is a
        <code class="literal">BIGINT</code> value, whereas the result with
        <code class="literal">_binary</code> is a binary string. Due to the
        difference in result types, the displayed values differ:
        High-order 0 digits are not displayed for the numeric result.
</p>
</div>
<div class="section">
<div class="titlepage">
<div>
<div>
<h3 class="title"><a name="boolean-literals"></a>9.1.6 Boolean Literals</h3>

</div>

</div>

</div>
<a class="indexterm" name="idm140091700130448"></a><a class="indexterm" name="idm140091700128960"></a><a class="indexterm" name="idm140091700127888"></a><a class="indexterm" name="idm140091700126816"></a><p>
        The constants <code class="literal">TRUE</code> and
        <code class="literal">FALSE</code> evaluate to <code class="literal">1</code> and
        <code class="literal">0</code>, respectively. The constant names can be
        written in any lettercase.
      </p><pre data-lang="sql" class="programlisting">
mysql&gt; <strong class="userinput"><code>SELECT TRUE, true, FALSE, false;</code></strong>
        -&gt; 1, 1, 0, 0
</pre>
</div>
<div class="section">
<div class="titlepage">
<div>
<div>
<h3 class="title"><a name="null-values"></a>9.1.7 NULL Values</h3>

</div>

</div>

</div>
<a class="indexterm" name="idm140091700116192"></a><a class="indexterm" name="idm140091700115120"></a><a class="indexterm" name="idm140091700113632"></a><p>
        The <code class="literal">NULL</code> value means <span class="quote">“<span class="quote">no data.</span>”</span>
        <code class="literal">NULL</code> can be written in any lettercase.
      </p><p>
        Be aware that the <code class="literal">NULL</code> value is different
        from values such as <code class="literal">0</code> for numeric types or
        the empty string for string types. For more information, see
        <a class="xref" href="error-handling.html#problems-with-null" title="B.4.4.3 Problems with NULL Values">Section B.4.4.3, “Problems with NULL Values”</a>.
      </p><p>
        For text file import or export operations performed with
        <a class="link" href="sql-syntax.html#load-data" title="13.2.7 LOAD DATA Syntax"><code class="literal">LOAD DATA</code></a> or
        <a class="link" href="sql-syntax.html#select-into" title="13.2.10.1 SELECT ... INTO Syntax"><code class="literal">SELECT ... INTO
        OUTFILE</code></a>, <code class="literal">NULL</code> is represented by the
        <code class="literal">\N</code> sequence. See <a class="xref" href="sql-syntax.html#load-data" title="13.2.7 LOAD DATA Syntax">Section 13.2.7, “LOAD DATA Syntax”</a>.
      </p><p>
        For sorting with <code class="literal">ORDER BY</code>,
        <code class="literal">NULL</code> values sort before other values for
        ascending sorts, after other values for descending sorts.
</p>
</div>

</div>
<div class="section">
<div class="titlepage">
<div>
<div>
<h2 class="title" style="clear: both"><a name="identifiers"></a>9.2 Schema Object Names</h2>

</div>

</div>

</div>
<div class="toc">
<dl class="toc"><dt><span class="section"><a href="language-structure.html#identifier-qualifiers">9.2.1 Identifier Qualifiers</a></span></dt><dt><span class="section"><a href="language-structure.html#identifier-case-sensitivity">9.2.2 Identifier Case Sensitivity</a></span></dt><dt><span class="section"><a href="language-structure.html#identifier-mapping">9.2.3 Mapping of Identifiers to File Names</a></span></dt><dt><span class="section"><a href="language-structure.html#function-resolution">9.2.4 Function Name Parsing and Resolution</a></span></dt></dl>
</div>
<a class="indexterm" name="idm140091700098928"></a><a class="indexterm" name="idm140091700097856"></a><a class="indexterm" name="idm140091700096784"></a><a class="indexterm" name="idm140091700095712"></a><a class="indexterm" name="idm140091700094224"></a><a class="indexterm" name="idm140091700092736"></a><a class="indexterm" name="idm140091700091248"></a><a class="indexterm" name="idm140091700089760"></a><a class="indexterm" name="idm140091700088272"></a><p>
      Certain objects within MySQL, including database, table, index,
      column, alias, view, stored procedure, partition, tablespace,
      resource group and other object names are known as identifiers.
      This section describes the permissible syntax for identifiers in
      MySQL. <a class="xref" href="language-structure.html#identifier-case-sensitivity" title="9.2.2 Identifier Case Sensitivity">Section 9.2.2, “Identifier Case Sensitivity”</a>, describes
      which types of identifiers are case-sensitive and under what
      conditions.
    </p><a class="indexterm" name="idm140091700085264"></a><a class="indexterm" name="idm140091700083776"></a><a class="indexterm" name="idm140091700082704"></a><a class="indexterm" name="idm140091700081664"></a><p>
      An identifier may be quoted or unquoted. If an identifier contains
      special characters or is a reserved word, you
      <span class="emphasis"><em>must</em></span> quote it whenever you refer to it.
      (Exception: A reserved word that follows a period in a qualified
      name must be an identifier, so it need not be quoted.) Reserved
      words are listed at <a class="xref" href="language-structure.html#keywords" title="9.3 Keywords and Reserved Words">Section 9.3, “Keywords and Reserved Words”</a>.
    </p><p>
      Identifiers are converted to Unicode internally. They may contain
      these characters:
</p>
<div class="itemizedlist">
<ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>
          Permitted characters in unquoted identifiers:
</p>
<div class="itemizedlist">
<ul class="itemizedlist" style="list-style-type: circle; "><li class="listitem"><p>
              ASCII: [0-9,a-z,A-Z$_] (basic Latin letters, digits 0-9,
              dollar, underscore)
            </p></li><li class="listitem"><p>
              Extended: U+0080 .. U+FFFF
</p></li></ul>
</div>
</li><li class="listitem"><p>
          Permitted characters in quoted identifiers include the full
          Unicode Basic Multilingual Plane (BMP), except U+0000:
</p>
<div class="itemizedlist">
<ul class="itemizedlist" style="list-style-type: circle; "><li class="listitem"><p>
              ASCII: U+0001 .. U+007F
            </p></li><li class="listitem"><p>
              Extended: U+0080 .. U+FFFF
</p></li></ul>
</div>
</li><li class="listitem"><p>
          ASCII NUL (U+0000) and supplementary characters (U+10000 and
          higher) are not permitted in quoted or unquoted identifiers.
        </p></li><li class="listitem"><p>
          Identifiers may begin with a digit but unless quoted may not
          consist solely of digits.
        </p></li><li class="listitem"><p>
          Database, table, and column names cannot end with space
          characters.
</p></li></ul>
</div>
<p>
      The identifier quote character is the backtick
      (<code class="literal">`</code>):
    </p><pre data-lang="sql" class="programlisting">
mysql&gt; <strong class="userinput"><code>SELECT * FROM `select` WHERE `select`.id &gt; 100;</code></strong>
</pre><p>
      If the <a class="link" href="server-administration.html#sqlmode_ansi_quotes"><code class="literal">ANSI_QUOTES</code></a> SQL mode is
      enabled, it is also permissible to quote identifiers within double
      quotation marks:
    </p><pre data-lang="sql" class="programlisting">
mysql&gt; <strong class="userinput"><code>CREATE TABLE "test" (col INT);</code></strong>
ERROR 1064: You have an error in your SQL syntax...
mysql&gt; <strong class="userinput"><code>SET sql_mode='ANSI_QUOTES';</code></strong>
mysql&gt; <strong class="userinput"><code>CREATE TABLE "test" (col INT);</code></strong>
Query OK, 0 rows affected (0.00 sec)
</pre><p>
      The <a class="link" href="server-administration.html#sqlmode_ansi_quotes"><code class="literal">ANSI_QUOTES</code></a> mode causes the
      server to interpret double-quoted strings as identifiers.
      Consequently, when this mode is enabled, string literals must be
      enclosed within single quotation marks. They cannot be enclosed
      within double quotation marks. The server SQL mode is controlled
      as described in <a class="xref" href="server-administration.html#sql-mode" title="5.1.11 Server SQL Modes">Section 5.1.11, “Server SQL Modes”</a>.
    </p><p>
      Identifier quote characters can be included within an identifier
      if you quote the identifier. If the character to be included
      within the identifier is the same as that used to quote the
      identifier itself, then you need to double the character. The
      following statement creates a table named <code class="literal">a`b</code>
      that contains a column named <code class="literal">c"d</code>:
    </p><pre data-lang="sql" class="programlisting">
mysql&gt; <strong class="userinput"><code>CREATE TABLE `a``b` (`c"d` INT);</code></strong>
</pre><a class="indexterm" name="idm140091700055024"></a><a class="indexterm" name="idm140091700053536"></a><p>
      In the select list of a query, a quoted column alias can be
      specified using identifier or string quoting characters:
    </p><pre data-lang="sql" class="programlisting">
mysql&gt; <strong class="userinput"><code>SELECT 1 AS `one`, 2 AS 'two';</code></strong>
+-----+-----+
| one | two |
+-----+-----+
|   1 |   2 |
+-----+-----+
</pre><p>
      Elsewhere in the statement, quoted references to the alias must
      use identifier quoting or the reference is treated as a string
      literal.
    </p><p>
      It is recommended that you do not use names that begin with
      <code class="literal"><em class="replaceable"><code>M</code></em>e</code> or
      <code class="literal"><em class="replaceable"><code>M</code></em>e<em class="replaceable"><code>N</code></em></code>,
      where <em class="replaceable"><code>M</code></em> and
      <em class="replaceable"><code>N</code></em> are integers. For example, avoid
      using <code class="literal">1e</code> as an identifier, because an
      expression such as <code class="literal">1e+3</code> is ambiguous. Depending
      on context, it might be interpreted as the expression <code class="literal">1e
      + 3</code> or as the number <code class="literal">1e+3</code>.
    </p><p>
      Be careful when using <a class="link" href="functions.html#function_md5"><code class="literal">MD5()</code></a> to
      produce table names because it can produce names in illegal or
      ambiguous formats such as those just described.
    </p><p>
      A user variable cannot be used directly in an SQL statement as an
      identifier or as part of an identifier. See
      <a class="xref" href="language-structure.html#user-variables" title="9.4 User-Defined Variables">Section 9.4, “User-Defined Variables”</a>, for more information and
      examples of workarounds.
    </p><p>
      Special characters in database and table names are encoded in the
      corresponding file system names as described in
      <a class="xref" href="language-structure.html#identifier-mapping" title="9.2.3 Mapping of Identifiers to File Names">Section 9.2.3, “Mapping of Identifiers to File Names”</a>.
    </p><p>
      The following table describes the maximum length for each type of
      identifier.
</p>
<div class="informaltable">
<table summary="The maximum length for each type of MySQL object identifier."><col width="15%"><col width="15%"><thead><tr>
          <th scope="col">Identifier Type</th>
          <th scope="col">Maximum Length (characters)</th>
        </tr></thead><tbody><tr>
          <th scope="row" align="left">Database</th>
          <td>64 (<a class="link" href="mysql-cluster.html" title="Chapter 22 MySQL NDB Cluster 8.0"><code class="literal">NDB</code></a> storage engine: 63)</td>
        </tr><tr>
          <th scope="row" align="left">Table</th>
          <td>64 (<a class="link" href="mysql-cluster.html" title="Chapter 22 MySQL NDB Cluster 8.0"><code class="literal">NDB</code></a> storage engine: 63)</td>
        </tr><tr>
          <th scope="row" align="left">Column</th>
          <td>64</td>
        </tr><tr>
          <th scope="row" align="left">Index</th>
          <td>64</td>
        </tr><tr>
          <th scope="row" align="left">Constraint</th>
          <td>64</td>
        </tr><tr>
          <th scope="row" align="left">Stored Program</th>
          <td>64</td>
        </tr><tr>
          <th scope="row" align="left">View</th>
          <td>64</td>
        </tr><tr>
          <th scope="row" align="left">Tablespace</th>
          <td>64</td>
        </tr><tr>
          <th scope="row" align="left">Server</th>
          <td>64</td>
        </tr><tr>
          <th scope="row" align="left">Log File Group</th>
          <td>64</td>
        </tr><tr>
          <th scope="row" align="left">Alias</th>
          <td>256 (see exception following table)</td>
        </tr><tr>
          <th scope="row" align="left">Compound Statement Label</th>
          <td>16</td>
        </tr><tr>
          <th scope="row" align="left">User-Defined Variable</th>
          <td>64</td>
        </tr><tr>
          <th scope="row" align="left">Resource Group</th>
          <td>64</td>
</tr></tbody></table>
</div>
<p>
      Aliases for column names in <a class="link" href="sql-syntax.html#create-view" title="13.1.23 CREATE VIEW Syntax"><code class="literal">CREATE
      VIEW</code></a> statements are checked against the maximum column
      length of 64 characters (not the maximum alias length of 256
      characters).
    </p><p>
      Identifiers are stored using Unicode (UTF-8). This applies to
      identifiers in table definitions and to identifiers stored in the
      grant tables in the <code class="literal">mysql</code> database. The sizes
      of the identifier string columns in the grant tables are measured
      in characters. You can use multibyte characters without reducing
      the number of characters permitted for values stored in these
      columns. As indicated earlier, the permissible Unicode characters
      are those in the Basic Multilingual Plane (BMP). Supplementary
      characters are not permitted.
    </p><p>
      NDB Cluster imposes a maximum length of 63 characters for names of
      databases and tables. See
      <a class="xref" href="mysql-cluster.html#mysql-cluster-limitations-database-objects" title="22.1.7.5 Limits Associated with Database Objects in NDB Cluster">Section 22.1.7.5, “Limits Associated with Database Objects in NDB Cluster”</a>.
</p>
<div class="section">

<div class="titlepage">
<div>
<div>
<h3 class="title"><a name="identifier-qualifiers"></a>9.2.1 Identifier Qualifiers</h3>
</div>
</div>
</div>
<p>
        Object names may be unqualified or qualified. An unqualified
        name is permitted in contexts where interpretation of the name
        is unambiguous. A qualified name includes at least one qualifier
        to clarify the interpretive context by overriding a default
        context or providing missing context.
      </p><p>
        For example, this statement creates a table using the
        unqualified name <code class="literal">t1</code>:
      </p><pre data-lang="sql" class="programlisting">
CREATE TABLE t1 (i INT);
</pre><p>
        Because <code class="literal">t1</code> includes no qualifier to specify a
        database, the statement creates the table in the default
        database. If there is no default database, an error occurs.
      </p><p>
        This statement creates a table using the qualified name
        <code class="literal">db1.t1</code>:
      </p><pre data-lang="sql" class="programlisting">
CREATE TABLE db1.t1 (i INT);
</pre><p>
        Because <code class="literal">db1.t1</code> includes a database qualifier
        <code class="literal">db1</code>, the statement creates
        <code class="literal">t1</code> in the database named
        <code class="literal">db1</code>, regardless of the default database. The
        qualifier <span class="emphasis"><em>must</em></span> be specified if there is no
        default database. The qualifier <span class="emphasis"><em>may</em></span> be
        specified if there is a default database, to specify a database
        different from the default, or to make the database explicit if
        the default is the same as the one specified.
      </p><p>
        Qualifiers have these characteristics:
</p>
<div class="itemizedlist">
<ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>
            An unqualified name consists of a single identifier. A
            qualified name consists of multiple identifiers.
          </p></li><li class="listitem"><p>
            The components of a multiple-part name must be separated by
            period (<code class="literal">.</code>) characters. The initial parts
            of a multiple-part name act as qualifiers that affect the
            context within which to interpret the final identifier.
          </p></li><li class="listitem"><p>
            The qualifier character is a separate token and need not be
            contiguous with the associated identifiers. For example,
            <em class="replaceable"><code>tbl_name.col_name</code></em> and
            <em class="replaceable"><code>tbl_name . col_name</code></em> are
            equivalent.
          </p></li><li class="listitem"><p>
            If any components of a multiple-part name require quoting,
            quote them individually rather than quoting the name as a
            whole. For example, write
            <code class="literal">`my-table`.`my-column`</code>, not
            <code class="literal">`my-table.my-column`</code>.
          </p></li><li class="listitem"><p>
            A reserved word that follows a period in a qualified name
            must be an identifier, so in that context it need not be
            quoted.
</p></li></ul>
</div>
<p>
        The permitted qualifiers for object names depend on the object
        type:
</p>
<div class="itemizedlist">
<ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>
            A database name is fully qualified and takes no qualifier:
          </p><pre data-lang="sql" class="programlisting">
CREATE DATABASE db1;
</pre></li><li class="listitem"><p>
            A table, view, or stored program name may be given a
            database-name qualifier. Examples of unqualified and
            qualified names in <code class="literal">CREATE</code> statements:
          </p><pre data-lang="sql" class="programlisting">
CREATE TABLE mytable ...;
CREATE VIEW myview ...;
CREATE PROCEDURE myproc ...;
CREATE FUNCTION myfunc ...;
CREATE EVENT myevent ...;

CREATE TABLE mydb.mytable ...;
CREATE VIEW mydb.myview ...;
CREATE PROCEDURE mydb.myproc ...;
CREATE FUNCTION mydb.myfunc ...;
CREATE EVENT mydb.myevent ...;
</pre></li><li class="listitem"><p>
            A trigger is associated with a table, so any qualifier
            applies to the table name:
          </p><pre data-lang="sql" class="programlisting">
CREATE TRIGGER mytrigger ... ON mytable ...;

CREATE TRIGGER mytrigger ... ON mydb.mytable ...;
</pre></li><li class="listitem"><p>
            A column name may be given multiple qualifiers to indicate
            context in statements that reference it, as shown in the
            following table.
</p>
<div class="informaltable">
<table summary="Column reference formats that can be used to refer to table columns."><col width="35%"><col width="65%"><thead><tr>
                <th scope="col">Column Reference</th>
                <th scope="col">Meaning</th>
              </tr></thead><tbody><tr>
                <td scope="row"><em class="replaceable"><code>col_name</code></em></td>
                <td>Column <em class="replaceable"><code>col_name</code></em> from whichever table used in
                  the statement contains a column of that name</td>
              </tr><tr>
                <td scope="row"><em class="replaceable"><code>tbl_name.col_name</code></em></td>
                <td>Column <em class="replaceable"><code>col_name</code></em> from table
                  <em class="replaceable"><code>tbl_name</code></em> of the default
                  database</td>
              </tr><tr>
                <td scope="row"><em class="replaceable"><code>db_name.tbl_name.col_name</code></em></td>
                <td>Column <em class="replaceable"><code>col_name</code></em> from table
                  <em class="replaceable"><code>tbl_name</code></em> of the database
                  <em class="replaceable"><code>db_name</code></em></td>
</tr></tbody></table>
</div>
<p>
            In other words, a column name may be given a table-name
            qualifier, which itself may be given a database-name
            qualifier. Examples of unqualified and qualified column
            references in <code class="literal">SELECT</code> statements:
          </p><pre data-lang="sql" class="programlisting">
SELECT c1 FROM mytable
WHERE c2 &gt; 100;

SELECT mytable.c1 FROM mytable
WHERE mytable.c2 &gt; 100;

SELECT mydb.mytable.c1 FROM mydb.mytable
WHERE mydb.mytable.c2 &gt; 100;
</pre></li></ul>
</div>
<p>
        You need not specify a qualifier for an object reference in a
        statement unless the unqualified reference is ambiguous. Suppose
        that column <code class="literal">c1</code> occurs only in table
        <code class="literal">t1</code>, <code class="literal">c2</code> only in
        <code class="literal">t2</code>, and <code class="literal">c</code> in both
        <code class="literal">t1</code> and <code class="literal">t2</code>. Any unqualified
        reference to <code class="literal">c</code> is ambiguous in a statement
        that refers to both tables and must be qualified as
        <code class="literal">t1.c</code> or <code class="literal">t2.c</code> to indicate
        which table you mean:
      </p><pre data-lang="sql" class="programlisting">
SELECT c1, c2, t1.c FROM t1 INNER JOIN t2
WHERE t2.c &gt; 100;
</pre><p>
        Similarly, to retrieve from a table <code class="literal">t</code> in
        database <code class="literal">db1</code> and from a table
        <code class="literal">t</code> in database <code class="literal">db2</code> in the
        same statement, you must qualify the table references: For
        references to columns in those tables, qualifiers are required
        only for column names that appear in both tables. Suppose that
        column <code class="literal">c1</code> occurs only in table
        <code class="literal">db1.t</code>, <code class="literal">c2</code> only in
        <code class="literal">db2.t</code>, and <code class="literal">c</code> in both
        <code class="literal">db1.t</code> and <code class="literal">db2.t</code>. In this
        case, <code class="literal">c</code> is ambiguous and must be qualified
        but <code class="literal">c1</code> and <code class="literal">c2</code> need not be:
      </p><pre data-lang="sql" class="programlisting">
SELECT c1, c2, db1.t.c FROM db1.t INNER JOIN db2.t
WHERE db2.t.c &gt; 100;
</pre><p>
        Table aliases enable qualified column references to be written
        more simply:
      </p><pre data-lang="sql" class="programlisting">
SELECT c1, c2, t1.c FROM db1.t AS t1 INNER JOIN db2.t AS t2
WHERE t2.c &gt; 100;
</pre>
</div>
<div class="section">
<div class="titlepage">
<div>
<div>
<h3 class="title"><a name="identifier-case-sensitivity"></a>9.2.2 Identifier Case Sensitivity</h3>

</div>

</div>

</div>
<a class="indexterm" name="idm140091699917280"></a><a class="indexterm" name="idm140091699915792"></a><a class="indexterm" name="idm140091699914304"></a><a class="indexterm" name="idm140091699912816"></a><a class="indexterm" name="idm140091699911328"></a><a class="indexterm" name="idm140091699909840"></a><a class="indexterm" name="idm140091699908352"></a><a class="indexterm" name="idm140091699906864"></a><a class="indexterm" name="idm140091699905376"></a><p>
        In MySQL, databases correspond to directories within the data
        directory. Each table within a database corresponds to at least
        one file within the database directory (and possibly more,
        depending on the storage engine). Triggers also correspond to
        files. Consequently, the case sensitivity of the underlying
        operating system plays a part in the case sensitivity of
        database, table, and trigger names. This means such names are
        not case-sensitive in Windows, but are case-sensitive in most
        varieties of Unix. One notable exception is macOS, which is
        Unix-based but uses a default file system type (HFS+) that is
        not case-sensitive. However, macOS also supports UFS volumes,
        which are case-sensitive just as on any Unix. See
        <a class="xref" href="introduction.html#extensions-to-ansi" title="1.8.1 MySQL Extensions to Standard SQL">Section 1.8.1, “MySQL Extensions to Standard SQL”</a>. The
        <a class="link" href="server-administration.html#sysvar_lower_case_table_names"><code class="literal">lower_case_table_names</code></a> system
        variable also affects how the server handles identifier case
        sensitivity, as described later in this section.
</p>
<div class="note" style="margin-left: 0.5in; margin-right: 0.5in;">

<div class="admon-title">
Note
</div>
<p>
          Although database, table, and trigger names are not case
          sensitive on some platforms, you should not refer to one of
          these using different cases within the same statement. The
          following statement would not work because it refers to a
          table both as <code class="literal">my_table</code> and as
          <code class="literal">MY_TABLE</code>:
        </p><pre data-lang="sql" class="programlisting">
mysql&gt; <strong class="userinput"><code>SELECT * FROM my_table WHERE MY_TABLE.col=1;</code></strong>
</pre>
</div>
<p>
        Column, index, stored routine, event, and resource group names
        are not case-sensitive on any platform, nor are column aliases.
      </p><p>
        However, names of logfile groups are case-sensitive. This
        differs from standard SQL.
      </p><p>
        By default, table aliases are case-sensitive on Unix, but not so
        on Windows or macOS. The following statement would not work on
        Unix, because it refers to the alias both as
        <code class="literal">a</code> and as <code class="literal">A</code>:
      </p><pre data-lang="sql" class="programlisting">
mysql&gt; <strong class="userinput"><code>SELECT <em class="replaceable"><code>col_name</code></em> FROM <em class="replaceable"><code>tbl_name</code></em> AS a</code></strong>
       <strong class="userinput"><code>WHERE a.<em class="replaceable"><code>col_name</code></em> = 1 OR A.<em class="replaceable"><code>col_name</code></em> = 2;</code></strong>
</pre><p>
        However, this same statement is permitted on Windows. To avoid
        problems caused by such differences, it is best to adopt a
        consistent convention, such as always creating and referring to
        databases and tables using lowercase names. This convention is
        recommended for maximum portability and ease of use.
      </p><p>
        How table and database names are stored on disk and used in
        MySQL is affected by the
        <a class="link" href="server-administration.html#sysvar_lower_case_table_names"><code class="literal">lower_case_table_names</code></a> system
        variable.
        <a class="link" href="server-administration.html#sysvar_lower_case_table_names"><code class="literal">lower_case_table_names</code></a> can take
        the values shown in the following table. This variable does
        <span class="emphasis"><em>not</em></span> affect case sensitivity of trigger
        identifiers. On Unix, the default value of
        <a class="link" href="server-administration.html#sysvar_lower_case_table_names"><code class="literal">lower_case_table_names</code></a> is 0. On
        Windows, the default value is 1. On macOS, the default value is
        2.
      </p><p>
        <a class="link" href="server-administration.html#sysvar_lower_case_table_names"><code class="literal">lower_case_table_names</code></a> can only
        be configured when initializing the server. Changing the
        <a class="link" href="server-administration.html#sysvar_lower_case_table_names"><code class="literal">lower_case_table_names</code></a> setting
        after the server is initialized is prohibited.
</p>
<div class="informaltable">
<table summary="Values for the lower_case_table_names system variable."><col width="10%"><col width="90%"><thead><tr>
            <th scope="col">Value</th>
            <th scope="col">Meaning</th>
          </tr></thead><tbody><tr>
            <td scope="row"><code class="literal">0</code></td>
            <td>Table and database names are stored on disk using the lettercase
              specified in the <a class="link" href="sql-syntax.html#create-table" title="13.1.20 CREATE TABLE Syntax"><code class="literal">CREATE
              TABLE</code></a> or <a class="link" href="sql-syntax.html#create-database" title="13.1.12 CREATE DATABASE Syntax"><code class="literal">CREATE
              DATABASE</code></a> statement. Name comparisons are case
              sensitive. You should <span class="emphasis"><em>not</em></span> set this
              variable to 0 if you are running MySQL on a system that
              has case-insensitive file names (such as Windows or
              macOS). If you force this variable to 0 with
              <a class="link" href="server-administration.html#sysvar_lower_case_table_names"><code class="option">--lower-case-table-names=0</code></a>
              on a case-insensitive file system and access
              <code class="literal">MyISAM</code> tablenames using different
              lettercases, index corruption may result.</td>
          </tr><tr>
            <td scope="row"><code class="literal">1</code></td>
            <td>Table names are stored in lowercase on disk and name comparisons are not
              case-sensitive. MySQL converts all table names to
              lowercase on storage and lookup. This behavior also
              applies to database names and table aliases.</td>
          </tr><tr>
            <td scope="row"><code class="literal">2</code></td>
            <td>Table and database names are stored on disk using the lettercase
              specified in the <a class="link" href="sql-syntax.html#create-table" title="13.1.20 CREATE TABLE Syntax"><code class="literal">CREATE
              TABLE</code></a> or <a class="link" href="sql-syntax.html#create-database" title="13.1.12 CREATE DATABASE Syntax"><code class="literal">CREATE
              DATABASE</code></a> statement, but MySQL converts them to
              lowercase on lookup. Name comparisons are not case
              sensitive. This works <span class="emphasis"><em>only</em></span> on file
              systems that are not case-sensitive!
              <code class="literal">InnoDB</code> table names and view names are
              stored in lowercase, as for
              <code class="literal">lower_case_table_names=1</code>.</td>
</tr></tbody></table>
</div>
<p>
        If you are using MySQL on only one platform, you do not normally
        have to use a
        <a class="link" href="server-administration.html#sysvar_lower_case_table_names"><code class="literal">lower_case_table_names</code></a> setting
        other than the default. However, you may encounter difficulties
        if you want to transfer tables between platforms that differ in
        file system case sensitivity. For example, on Unix, you can have
        two different tables named <code class="literal">my_table</code> and
        <code class="literal">MY_TABLE</code>, but on Windows these two names are
        considered identical. To avoid data transfer problems arising
        from lettercase of database or table names, you have two
        options:
</p>
<div class="itemizedlist">
<ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>
            Use <code class="literal">lower_case_table_names=1</code> on all
            systems. The main disadvantage with this is that when you
            use <a class="link" href="sql-syntax.html#show-tables" title="13.7.6.37 SHOW TABLES Syntax"><code class="literal">SHOW TABLES</code></a> or
            <a class="link" href="sql-syntax.html#show-databases" title="13.7.6.14 SHOW DATABASES Syntax"><code class="literal">SHOW DATABASES</code></a>, you do not
            see the names in their original lettercase.
          </p></li><li class="listitem"><p>
            Use <code class="literal">lower_case_table_names=0</code> on Unix and
            <code class="literal">lower_case_table_names=2</code> on Windows. This
            preserves the lettercase of database and table names. The
            disadvantage of this is that you must ensure that your
            statements always refer to your database and table names
            with the correct lettercase on Windows. If you transfer your
            statements to Unix, where lettercase is significant, they do
            not work if the lettercase is incorrect.
          </p><p>
            <span class="bold"><strong>Exception</strong></span>: If you are using
            <code class="literal">InnoDB</code> tables and you are trying to avoid
            these data transfer problems, you should use
            <a class="link" href="server-administration.html#sysvar_lower_case_table_names"><code class="literal">lower_case_table_names=1</code></a> on
            all platforms to force names to be converted to lowercase.
</p></li></ul>
</div>
<p>
        Object names may be considered duplicates if their uppercase
        forms are equal according to a binary collation. That is true
        for names of cursors, conditions, procedures, functions,
        savepoints, stored routine parameters, stored program local
        variables, and plugins. It is not true for names of columns,
        constraints, databases, partitions, statements prepared with
        <a class="link" href="sql-syntax.html#prepare" title="13.5.1 PREPARE Syntax"><code class="literal">PREPARE</code></a>, tables, triggers, users,
        and user-defined variables.
      </p><p>
        File system case sensitivity can affect searches in string
        columns of <code class="literal">INFORMATION_SCHEMA</code> tables. For
        more information, see
        <a class="xref" href="charset.html#charset-collation-information-schema" title="10.8.7 Using Collation in INFORMATION_SCHEMA Searches">Section 10.8.7, “Using Collation in INFORMATION_SCHEMA Searches”</a>.
</p>
</div>
<div class="section">
<div class="titlepage">
<div>
<div>
<h3 class="title"><a name="identifier-mapping"></a>9.2.3 Mapping of Identifiers to File Names</h3>

</div>

</div>

</div>
<p>
        There is a correspondence between database and table identifiers
        and names in the file system. For the basic structure, MySQL
        represents each database as a directory in the data directory,
        and depending upon the storage engine, each table may be
        represented by one or more files in the appropriate database
        directory.
      </p><p>
        For the data and index files, the exact representation on disk
        is storage engine specific. These files may be stored in the
        database directory, or the information may be stored in a
        separate file. <code class="literal">InnoDB</code> data is stored in the
        InnoDB data files. If you are using tablespaces with
        <code class="literal">InnoDB</code>, then the specific tablespace files
        you create are used instead.
      </p><p>
        Any character is legal in database or table identifiers except
        ASCII NUL (<code class="literal">X'00'</code>). MySQL encodes any
        characters that are problematic in the corresponding file system
        objects when it creates database directories or table files:
</p>
<div class="itemizedlist">
<ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>
            Basic Latin letters (<code class="literal">a..zA..Z</code>), digits
            (<code class="literal">0..9</code>) and underscore
            (<code class="literal">_</code>) are encoded as is. Consequently,
            their case sensitivity directly depends on file system
            features.
          </p></li><li class="listitem"><p>
            All other national letters from alphabets that have
            uppercase/lowercase mapping are encoded as shown in the
            following table. Values in the Code Range column are UCS-2
            values.
</p>
<div class="informaltable">
<table summary="The encoding for national letters from alphabets that have uppercase/lowercase mapping, excluding basic Latin letters (a..zA..Z), digits (0..9) and underscore (_), which are encoded as is."><col width="15%"><col width="15%"><col width="15%"><col width="15%"><col width="15%"><col width="25%"><thead><tr>
                <th scope="col">Code Range</th>
                <th scope="col">Pattern</th>
                <th scope="col">Number</th>
                <th scope="col">Used</th>
                <th scope="col">Unused</th>
                <th scope="col">Blocks</th>
              </tr></thead><tbody><tr>
                <td scope="row">00C0..017F</td>
                <td>[@][0..4][g..z]</td>
                <td>5*20= 100</td>
                <td>97</td>
                <td>3</td>
                <td>Latin-1 Supplement + Latin Extended-A</td>
              </tr><tr>
                <td scope="row">0370..03FF</td>
                <td>[@][5..9][g..z]</td>
                <td>5*20= 100</td>
                <td>88</td>
                <td>12</td>
                <td>Greek and Coptic</td>
              </tr><tr>
                <td scope="row">0400..052F</td>
                <td>[@][g..z][0..6]</td>
                <td>20*7= 140</td>
                <td>137</td>
                <td>3</td>
                <td>Cyrillic + Cyrillic Supplement</td>
              </tr><tr>
                <td scope="row">0530..058F</td>
                <td>[@][g..z][7..8]</td>
                <td>20*2= 40</td>
                <td>38</td>
                <td>2</td>
                <td>Armenian</td>
              </tr><tr>
                <td scope="row">2160..217F</td>
                <td>[@][g..z][9]</td>
                <td>20*1= 20</td>
                <td>16</td>
                <td>4</td>
                <td>Number Forms</td>
              </tr><tr>
                <td scope="row">0180..02AF</td>
                <td>[@][g..z][a..k]</td>
                <td>20*11=220</td>
                <td>203</td>
                <td>17</td>
                <td>Latin Extended-B + IPA Extensions</td>
              </tr><tr>
                <td scope="row">1E00..1EFF</td>
                <td>[@][g..z][l..r]</td>
                <td>20*7= 140</td>
                <td>136</td>
                <td>4</td>
                <td>Latin Extended Additional</td>
              </tr><tr>
                <td scope="row">1F00..1FFF</td>
                <td>[@][g..z][s..z]</td>
                <td>20*8= 160</td>
                <td>144</td>
                <td>16</td>
                <td>Greek Extended</td>
              </tr><tr>
                <td scope="row">.... ....</td>
                <td>[@][a..f][g..z]</td>
                <td>6*20= 120</td>
                <td>0</td>
                <td>120</td>
                <td>RESERVED</td>
              </tr><tr>
                <td scope="row">24B6..24E9</td>
                <td>[@][@][a..z]</td>
                <td>26</td>
                <td>26</td>
                <td>0</td>
                <td>Enclosed Alphanumerics</td>
              </tr><tr>
                <td scope="row">FF21..FF5A</td>
                <td>[@][a..z][@]</td>
                <td>26</td>
                <td>26</td>
                <td>0</td>
                <td>Halfwidth and Fullwidth forms</td>
</tr></tbody></table>
</div>
<p>
            One of the bytes in the sequence encodes lettercase. For
            example: <code class="literal">LATIN CAPITAL LETTER A WITH
            GRAVE</code> is encoded as <code class="literal">@0G</code>,
            whereas <code class="literal">LATIN SMALL LETTER A WITH GRAVE</code>
            is encoded as <code class="literal">@0g</code>. Here the third byte
            (<code class="literal">G</code> or <code class="literal">g</code>) indicates
            lettercase. (On a case-insensitive file system, both letters
            will be treated as the same.)
          </p><p>
            For some blocks, such as Cyrillic, the second byte
            determines lettercase. For other blocks, such as Latin1
            Supplement, the third byte determines lettercase. If two
            bytes in the sequence are letters (as in Greek Extended),
            the leftmost letter character stands for lettercase. All
            other letter bytes must be in lowercase.
          </p></li><li class="listitem"><p>
            All nonletter characters except underscore
            (<code class="literal">_</code>), as well as letters from alphabets
            that do not have uppercase/lowercase mapping (such as
            Hebrew) are encoded using hexadecimal representation using
            lowercase letters for hexadecimal digits
            <code class="literal">a..f</code>:
          </p><pre data-lang="clike" class="programlisting">
0x003F -&gt; @003f
0xFFFF -&gt; @ffff
</pre><p>
            The hexadecimal values correspond to character values in the
            <code class="literal">ucs2</code> double-byte character set.
</p></li></ul>
</div>
<p>
        On Windows, some names such as <code class="literal">nul</code>,
        <code class="literal">prn</code>, and <code class="literal">aux</code> are encoded
        by appending <code class="literal">@@@</code> to the name when the server
        creates the corresponding file or directory. This occurs on all
        platforms for portability of the corresponding database object
        between platforms.
</p>
</div>
<div class="section">
<div class="titlepage">
<div>
<div>
<h3 class="title"><a name="function-resolution"></a>9.2.4 Function Name Parsing and Resolution</h3>

</div>

</div>

</div>
<a class="indexterm" name="idm140091699733024"></a><a class="indexterm" name="idm140091699731568"></a><p>
        MySQL supports built-in (native) functions, user-defined
        functions (UDFs), and stored functions. This section describes
        how the server recognizes whether the name of a built-in
        function is used as a function call or as an identifier, and how
        the server determines which function to use in cases when
        functions of different types exist with a given name.
</p>
<div class="itemizedlist">
<ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p><a class="xref" href="language-structure.html#function-name-parsing" title="Built-In Function Name Parsing">Built-In Function Name Parsing</a></p></li><li class="listitem"><p><a class="xref" href="language-structure.html#function-name-resolution" title="Function Name Resolution">Function Name Resolution</a></p></li></ul>
</div>

<div class="simplesect">

<div class="titlepage">
<div>

<div class="simple">
<h4 class="title"><a name="function-name-parsing"></a>Built-In Function Name Parsing</h4>

</div>

</div>

</div>
<p>
          The parser uses default rules for parsing names of built-in
          functions. These rules can be changed by enabling the
          <a class="link" href="server-administration.html#sqlmode_ignore_space"><code class="literal">IGNORE_SPACE</code></a> SQL mode.
        </p><p>
          When the parser encounters a word that is the name of a
          built-in function, it must determine whether the name
          signifies a function call or is instead a nonexpression
          reference to an identifier such as a table or column name. For
          example, in the following statements, the first reference to
          <code class="literal">count</code> is a function call, whereas the
          second reference is a table name:
        </p><pre data-lang="sql" class="programlisting">
SELECT COUNT(*) FROM mytable;
CREATE TABLE count (i INT);
</pre><p>
          The parser should recognize the name of a built-in function as
          indicating a function call only when parsing what is expected
          to be an expression. That is, in nonexpression context,
          function names are permitted as identifiers.
        </p><p>
          However, some built-in functions have special parsing or
          implementation considerations, so the parser uses the
          following rules by default to distinguish whether their names
          are being used as function calls or as identifiers in
          nonexpression context:
</p>
<div class="itemizedlist">
<ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>
              To use the name as a function call in an expression, there
              must be no whitespace between the name and the following
              <code class="literal">(</code> parenthesis character.
            </p></li><li class="listitem"><p>
              Conversely, to use the function name as an identifier, it
              must not be followed immediately by a parenthesis.
</p></li></ul>
</div>
<p>
          The requirement that function calls be written with no
          whitespace between the name and the parenthesis applies only
          to the built-in functions that have special considerations.
          <code class="literal">COUNT</code> is one such name. The
          <code class="filename">sql/lex.h</code> source file lists the names of
          these special functions for which following whitespace
          determines their interpretation: names defined by the
          <code class="literal">SYM_FN()</code> macro in the
          <code class="literal">symbols[]</code> array.
        </p><p>
          The following list names the functions in MySQL
          8.0 that are affected by the
          <a class="link" href="server-administration.html#sqlmode_ignore_space"><code class="literal">IGNORE_SPACE</code></a> setting and
          listed as special in the <code class="filename">sql/lex.h</code> source
          file. You may find it easiest to treat the no-whitespace
          requirement as applying to all function calls.
</p>
<div class="itemizedlist">
<ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>
              <code class="literal">ADDDATE</code>
            </p></li><li class="listitem"><p>
              <code class="literal">BIT_AND</code>
            </p></li><li class="listitem"><p>
              <code class="literal">BIT_OR</code>
            </p></li><li class="listitem"><p>
              <code class="literal">BIT_XOR</code>
            </p></li><li class="listitem"><p>
              <code class="literal">CAST</code>
            </p></li><li class="listitem"><p>
              <code class="literal">COUNT</code>
            </p></li><li class="listitem"><p>
              <code class="literal">CURDATE</code>
            </p></li><li class="listitem"><p>
              <code class="literal">CURTIME</code>
            </p></li><li class="listitem"><p>
              <code class="literal">DATE_ADD</code>
            </p></li><li class="listitem"><p>
              <code class="literal">DATE_SUB</code>
            </p></li><li class="listitem"><p>
              <code class="literal">EXTRACT</code>
            </p></li><li class="listitem"><p>
              <code class="literal">GROUP_CONCAT</code>
            </p></li><li class="listitem"><p>
              <code class="literal">MAX</code>
            </p></li><li class="listitem"><p>
              <code class="literal">MID</code>
            </p></li><li class="listitem"><p>
              <code class="literal">MIN</code>
            </p></li><li class="listitem"><p>
              <code class="literal">NOW</code>
            </p></li><li class="listitem"><p>
              <code class="literal">POSITION</code>
            </p></li><li class="listitem"><p>
              <code class="literal">SESSION_USER</code>
            </p></li><li class="listitem"><p>
              <code class="literal">STD</code>
            </p></li><li class="listitem"><p>
              <code class="literal">STDDEV</code>
            </p></li><li class="listitem"><p>
              <code class="literal">STDDEV_POP</code>
            </p></li><li class="listitem"><p>
              <code class="literal">STDDEV_SAMP</code>
            </p></li><li class="listitem"><p>
              <code class="literal">SUBDATE</code>
            </p></li><li class="listitem"><p>
              <code class="literal">SUBSTR</code>
            </p></li><li class="listitem"><p>
              <code class="literal">SUBSTRING</code>
            </p></li><li class="listitem"><p>
              <code class="literal">SUM</code>
            </p></li><li class="listitem"><p>
              <code class="literal">SYSDATE</code>
            </p></li><li class="listitem"><p>
              <code class="literal">SYSTEM_USER</code>
            </p></li><li class="listitem"><p>
              <code class="literal">TRIM</code>
            </p></li><li class="listitem"><p>
              <code class="literal">VARIANCE</code>
            </p></li><li class="listitem"><p>
              <code class="literal">VAR_POP</code>
            </p></li><li class="listitem"><p>
              <code class="literal">VAR_SAMP</code>
</p></li></ul>
</div>
<p>
          For functions not listed as special in
          <code class="filename">sql/lex.h</code>, whitespace does not matter.
          They are interpreted as function calls only when used in
          expression context and may be used freely as identifiers
          otherwise. <code class="literal">ASCII</code> is one such name. However,
          for these nonaffected function names, interpretation may vary
          in expression context:
          <code class="literal"><em class="replaceable"><code>func_name</code></em> ()</code> is
          interpreted as a built-in function if there is one with the
          given name; if not,
          <code class="literal"><em class="replaceable"><code>func_name</code></em> ()</code> is
          interpreted as a user-defined function or stored function if
          one exists with that name.
        </p><p>
          The <a class="link" href="server-administration.html#sqlmode_ignore_space"><code class="literal">IGNORE_SPACE</code></a> SQL mode
          can be used to modify how the parser treats function names
          that are whitespace-sensitive:
</p>
<div class="itemizedlist">
<ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>
              With <a class="link" href="server-administration.html#sqlmode_ignore_space"><code class="literal">IGNORE_SPACE</code></a>
              disabled, the parser interprets the name as a function
              call when there is no whitespace between the name and the
              following parenthesis. This occurs even when the function
              name is used in nonexpression context:
            </p><pre data-lang="sql" class="programlisting">
mysql&gt; <strong class="userinput"><code>CREATE TABLE count(i INT);</code></strong>
ERROR 1064 (42000): You have an error in your SQL syntax ...
near 'count(i INT)'
</pre><p>
              To eliminate the error and cause the name to be treated as
              an identifier, either use whitespace following the name or
              write it as a quoted identifier (or both):
            </p><pre data-lang="sql" class="programlisting">
CREATE TABLE count (i INT);
CREATE TABLE `count`(i INT);
CREATE TABLE `count` (i INT);
</pre></li><li class="listitem"><p>
              With <a class="link" href="server-administration.html#sqlmode_ignore_space"><code class="literal">IGNORE_SPACE</code></a>
              enabled, the parser loosens the requirement that there be
              no whitespace between the function name and the following
              parenthesis. This provides more flexibility in writing
              function calls. For example, either of the following
              function calls are legal:
            </p><pre data-lang="sql" class="programlisting">
SELECT COUNT(*) FROM mytable;
SELECT COUNT (*) FROM mytable;
</pre><p>
              However, enabling
              <a class="link" href="server-administration.html#sqlmode_ignore_space"><code class="literal">IGNORE_SPACE</code></a> also has
              the side effect that the parser treats the affected
              function names as reserved words (see
              <a class="xref" href="language-structure.html#keywords" title="9.3 Keywords and Reserved Words">Section 9.3, “Keywords and Reserved Words”</a>). This means that a space
              following the name no longer signifies its use as an
              identifier. The name can be used in function calls with or
              without following whitespace, but causes a syntax error in
              nonexpression context unless it is quoted. For example,
              with <a class="link" href="server-administration.html#sqlmode_ignore_space"><code class="literal">IGNORE_SPACE</code></a>
              enabled, both of the following statements fail with a
              syntax error because the parser interprets
              <code class="literal">count</code> as a reserved word:
            </p><pre data-lang="sql" class="programlisting">
CREATE TABLE count(i INT);
CREATE TABLE count (i INT);
</pre><p>
              To use the function name in nonexpression context, write
              it as a quoted identifier:
            </p><pre data-lang="sql" class="programlisting">
CREATE TABLE `count`(i INT);
CREATE TABLE `count` (i INT);
</pre></li></ul>
</div>
<p>
          To enable the <a class="link" href="server-administration.html#sqlmode_ignore_space"><code class="literal">IGNORE_SPACE</code></a>
          SQL mode, use this statement:
        </p><pre data-lang="sql" class="programlisting">
SET sql_mode = 'IGNORE_SPACE';
</pre><p>
          <a class="link" href="server-administration.html#sqlmode_ignore_space"><code class="literal">IGNORE_SPACE</code></a> is also enabled
          by certain other composite modes such as
          <a class="link" href="server-administration.html#sqlmode_ansi"><code class="literal">ANSI</code></a> that include it in
          their value:
        </p><pre data-lang="sql" class="programlisting">
SET sql_mode = 'ANSI';
</pre><p>
          Check <a class="xref" href="server-administration.html#sql-mode" title="5.1.11 Server SQL Modes">Section 5.1.11, “Server SQL Modes”</a>, to see which composite modes
          enable <a class="link" href="server-administration.html#sqlmode_ignore_space"><code class="literal">IGNORE_SPACE</code></a>.
        </p><p>
          To minimize the dependency of SQL code on the
          <a class="link" href="server-administration.html#sqlmode_ignore_space"><code class="literal">IGNORE_SPACE</code></a> setting, use
          these guidelines:
</p>
<div class="itemizedlist">
<ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>
              Avoid creating UDFs or stored functions that have the same
              name as a built-in function.
            </p></li><li class="listitem"><p>
              Avoid using function names in nonexpression context. For
              example, these statements use <code class="literal">count</code>
              (one of the affected function names affected by
              <a class="link" href="server-administration.html#sqlmode_ignore_space"><code class="literal">IGNORE_SPACE</code></a>), so they
              fail with or without whitespace following the name if
              <a class="link" href="server-administration.html#sqlmode_ignore_space"><code class="literal">IGNORE_SPACE</code></a> is enabled:
            </p><pre data-lang="sql" class="programlisting">
CREATE TABLE count(i INT);
CREATE TABLE count (i INT);
</pre><p>
              If you must use a function name in nonexpression context,
              write it as a quoted identifier:
            </p><pre data-lang="sql" class="programlisting">
CREATE TABLE `count`(i INT);
CREATE TABLE `count` (i INT);
</pre></li></ul>
</div>

</div>
<div class="simplesect">
<div class="titlepage">
<div>
<div class="simple">
<h4 class="title"><a name="function-name-resolution"></a>Function Name Resolution</h4>

</div>

</div>

</div>
<p>
          The following rules describe how the server resolves
          references to function names for function creation and
          invocation:
</p>
<div class="itemizedlist">
<ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>
              Built-in functions and user-defined functions
            </p><p>
              An error occurs if you try to create a UDF with the same
              name as a built-in function.
            </p></li><li class="listitem"><p>
              Built-in functions and stored functions
            </p><p>
              It is possible to create a stored function with the same
              name as a built-in function, but to invoke the stored
              function it is necessary to qualify it with a schema name.
              For example, if you create a stored function named
              <code class="literal">PI</code> in the <code class="literal">test</code>
              schema, invoke it as <code class="literal">test.PI()</code> because
              the server resolves <a class="link" href="functions.html#function_pi"><code class="literal">PI()</code></a>
              without a qualifier as a reference to the built-in
              function. The server generates a warning if the stored
              function name collides with a built-in function name. The
              warning can be displayed with <a class="link" href="sql-syntax.html#show-warnings" title="13.7.6.40 SHOW WARNINGS Syntax"><code class="literal">SHOW
              WARNINGS</code></a>.
            </p></li><li class="listitem"><p>
              User-defined functions and stored functions
            </p><p>
              User-defined functions and stored functions share the same
              namespace, so you cannot create a UDF and a stored
              function with the same name.
</p></li></ul>
</div>
<p>
          The preceding function name resolution rules have implications
          for upgrading to versions of MySQL that implement new built-in
          functions:
</p>
<div class="itemizedlist">
<ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>
              If you have already created a user-defined function with a
              given name and upgrade MySQL to a version that implements
              a new built-in function with the same name, the UDF
              becomes inaccessible. To correct this, use
              <a class="link" href="sql-syntax.html#drop-function" title="13.1.26 DROP FUNCTION Syntax"><code class="literal">DROP FUNCTION</code></a> to drop the
              UDF and <a class="link" href="sql-syntax.html#create-function" title="13.1.14 CREATE FUNCTION Syntax"><code class="literal">CREATE FUNCTION</code></a> to
              re-create the UDF with a different nonconflicting name.
              Then modify any affected code to use the new name.
            </p></li><li class="listitem"><p>
              If a new version of MySQL implements a built-in function
              with the same name as an existing stored function, you
              have two choices: Rename the stored function to use a
              nonconflicting name, or change calls to the function so
              that they use a schema qualifier (that is, use
              <code class="literal"><em class="replaceable"><code>schema_name</code></em>.<em class="replaceable"><code>func_name</code></em>()</code>
              syntax). In either case, modify any affected code
              accordingly.
</p></li></ul>
</div>

</div>

</div>

</div>
<div class="section">
<div class="titlepage">
<div>
<div>
<h2 class="title" style="clear: both"><a name="keywords"></a>9.3 Keywords and Reserved Words</h2>

</div>

</div>

</div>
<a class="indexterm" name="idm140091699601872"></a><a class="indexterm" name="idm140091699600832"></a><p>
      Keywords are words that have significance in SQL. Certain
      keywords, such as <a class="link" href="sql-syntax.html#select" title="13.2.10 SELECT Syntax"><code class="literal">SELECT</code></a>,
      <a class="link" href="sql-syntax.html#delete" title="13.2.2 DELETE Syntax"><code class="literal">DELETE</code></a>, or
      <a class="link" href="data-types.html#integer-types" title="11.2.1 Integer Types (Exact Value) - INTEGER, INT, SMALLINT, TINYINT, MEDIUMINT, BIGINT"><code class="literal">BIGINT</code></a>, are reserved and require
      special treatment for use as identifiers such as table and column
      names. This may also be true for the names of built-in functions.
    </p><p>
      Nonreserved keywords are permitted as identifiers without quoting.
      Reserved words are permitted as identifiers if you quote them as
      described in <a class="xref" href="language-structure.html#identifiers" title="9.2 Schema Object Names">Section 9.2, “Schema Object Names”</a>:
    </p><pre data-lang="sql" class="programlisting">
mysql&gt; <strong class="userinput"><code>CREATE TABLE interval (begin INT, end INT);</code></strong>
ERROR 1064 (42000): You have an error in your SQL syntax ...
near 'interval (begin INT, end INT)'
</pre><p>
      <code class="literal">BEGIN</code> and <code class="literal">END</code> are keywords
      but not reserved, so their use as identifiers does not require
      quoting. <code class="literal">INTERVAL</code> is a reserved keyword and
      must be quoted to be used as an identifier:
    </p><pre data-lang="sql" class="programlisting">
mysql&gt; <strong class="userinput"><code>CREATE TABLE `interval` (begin INT, end INT);</code></strong>
Query OK, 0 rows affected (0.01 sec)
</pre><p>
      Exception: A word that follows a period in a qualified name must
      be an identifier, so it need not be quoted even if it is reserved:
    </p><pre data-lang="sql" class="programlisting">
mysql&gt; <strong class="userinput"><code>CREATE TABLE mydb.interval (begin INT, end INT);</code></strong>
Query OK, 0 rows affected (0.01 sec)
</pre><p>
      Names of built-in functions are permitted as identifiers but may
      require care to be used as such. For example,
      <code class="literal">COUNT</code> is acceptable as a column name. However,
      by default, no whitespace is permitted in function invocations
      between the function name and the following <code class="literal">(</code>
      character. This requirement enables the parser to distinguish
      whether the name is used in a function call or in nonfunction
      context. For further details on recognition of function names, see
      <a class="xref" href="language-structure.html#function-resolution" title="9.2.4 Function Name Parsing and Resolution">Section 9.2.4, “Function Name Parsing and Resolution”</a>.
    </p><p>
      The <code class="literal">INFORMATION_SCHEMA.KEYWORDS</code> table lists the
      words considered keywords by MySQL and indicates whether they are
      reserved. See <a class="xref" href="information-schema.html#keywords-table" title="25.14 The INFORMATION_SCHEMA KEYWORDS Table">Section 25.14, “The INFORMATION_SCHEMA KEYWORDS Table”</a>.
</p>
<div class="itemizedlist">
<ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p><a class="xref" href="language-structure.html#keywords-in-current-series" title="MySQL 8.0 Keywords and Reserved Words">MySQL 8.0 Keywords and Reserved Words</a></p></li><li class="listitem"><p><a class="xref" href="language-structure.html#keywords-new-in-current-series" title="MySQL 8.0 New Keywords and Reserved Words">MySQL 8.0 New Keywords and Reserved Words</a></p></li><li class="listitem"><p><a class="xref" href="language-structure.html#keywords-removed-in-current-series" title="MySQL 8.0 Removed Keywords and Reserved Words">MySQL 8.0 Removed Keywords and Reserved Words</a></p></li></ul>
</div>

<div class="simplesect">

<div class="titlepage">
<div>

<div class="simple">
<h3 class="title"><a name="keywords-in-current-series"></a>MySQL 8.0 Keywords and Reserved Words</h3>

</div>

</div>

</div>
<p>
        The following list shows the keywords and reserved words in
        MySQL 8.0, along with changes to individual words
        from version to version. Reserved keywords are marked with (R).
        In addition, <code class="literal">_FILENAME</code> is reserved.
      </p><p>
        At some point, you might upgrade to a higher version, so it is a
        good idea to have a look at future reserved words, too. You can
        find these in the manuals that cover higher versions of MySQL.
        Most of the reserved words in the list are forbidden by standard
        SQL as column or table names (for example,
        <code class="literal">GROUP</code>). A few are reserved because MySQL
        needs them and uses a <span class="command"><strong>yacc</strong></span> parser.
      </p><p><a name="keywords-8-0-detailed-top"></a><a class="link" href="language-structure.html#keywords-8-0-detailed-A">A</a>
 | <a class="link" href="language-structure.html#keywords-8-0-detailed-B">B</a>
 | <a class="link" href="language-structure.html#keywords-8-0-detailed-C">C</a>
 | <a class="link" href="language-structure.html#keywords-8-0-detailed-D">D</a>
 | <a class="link" href="language-structure.html#keywords-8-0-detailed-E">E</a>
 | <a class="link" href="language-structure.html#keywords-8-0-detailed-F">F</a>
 | <a class="link" href="language-structure.html#keywords-8-0-detailed-G">G</a>
 | <a class="link" href="language-structure.html#keywords-8-0-detailed-H">H</a>
 | <a class="link" href="language-structure.html#keywords-8-0-detailed-I">I</a>
 | <a class="link" href="language-structure.html#keywords-8-0-detailed-J">J</a>
 | <a class="link" href="language-structure.html#keywords-8-0-detailed-K">K</a>
 | <a class="link" href="language-structure.html#keywords-8-0-detailed-L">L</a>
 | <a class="link" href="language-structure.html#keywords-8-0-detailed-M">M</a>
 | <a class="link" href="language-structure.html#keywords-8-0-detailed-N">N</a>
 | <a class="link" href="language-structure.html#keywords-8-0-detailed-O">O</a>
 | <a class="link" href="language-structure.html#keywords-8-0-detailed-P">P</a>
 | <a class="link" href="language-structure.html#keywords-8-0-detailed-Q">Q</a>
 | <a class="link" href="language-structure.html#keywords-8-0-detailed-R">R</a>
 | <a class="link" href="language-structure.html#keywords-8-0-detailed-S">S</a>
 | <a class="link" href="language-structure.html#keywords-8-0-detailed-T">T</a>
 | <a class="link" href="language-structure.html#keywords-8-0-detailed-U">U</a>
 | <a class="link" href="language-structure.html#keywords-8-0-detailed-V">V</a>
 | <a class="link" href="language-structure.html#keywords-8-0-detailed-W">W</a>
 | <a class="link" href="language-structure.html#keywords-8-0-detailed-X">X</a>
 | <a class="link" href="language-structure.html#keywords-8-0-detailed-Y">Y</a>
 | <a class="link" href="language-structure.html#keywords-8-0-detailed-Z">Z</a>
</p><p><a name="keywords-8-0-detailed-A"></a>A</p>
<div class="itemizedlist">
<ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p><code class="literal">ACCESSIBLE</code> (R)</p></li><li class="listitem"><p><code class="literal">ACCOUNT</code></p></li><li class="listitem"><p><code class="literal">ACTION</code></p></li><li class="listitem"><p><code class="literal">ACTIVE</code>; added in 8.0.14 (nonreserved)</p></li><li class="listitem"><p><code class="literal">ADD</code> (R)</p></li><li class="listitem"><p><code class="literal">ADMIN</code>; became nonreserved in 8.0.12</p></li><li class="listitem"><p><code class="literal">AFTER</code></p></li><li class="listitem"><p><code class="literal">AGAINST</code></p></li><li class="listitem"><p><code class="literal">AGGREGATE</code></p></li><li class="listitem"><p><code class="literal">ALGORITHM</code></p></li><li class="listitem"><p><code class="literal">ALL</code> (R)</p></li><li class="listitem"><p><code class="literal">ALTER</code> (R)</p></li><li class="listitem"><p><code class="literal">ALWAYS</code></p></li><li class="listitem"><p><code class="literal">ANALYSE</code>; removed in 8.0.1</p></li><li class="listitem"><p><code class="literal">ANALYZE</code> (R)</p></li><li class="listitem"><p><code class="literal">AND</code> (R)</p></li><li class="listitem"><p><code class="literal">ANY</code></p></li><li class="listitem"><p><code class="literal">ARRAY</code> (R); added in 8.0.17 (reserved)</p></li><li class="listitem"><p><code class="literal">AS</code> (R)</p></li><li class="listitem"><p><code class="literal">ASC</code> (R)</p></li><li class="listitem"><p><code class="literal">ASCII</code></p></li><li class="listitem"><p><code class="literal">ASENSITIVE</code> (R)</p></li><li class="listitem"><p><code class="literal">AT</code></p></li><li class="listitem"><p><code class="literal">AUTOEXTEND_SIZE</code></p></li><li class="listitem"><p><code class="literal">AUTO_INCREMENT</code></p></li><li class="listitem"><p><code class="literal">AVG</code></p></li><li class="listitem"><p><code class="literal">AVG_ROW_LENGTH</code></p></li></ul>
</div>
<p><a name="keywords-8-0-detailed-B"></a>B</p>
<div class="itemizedlist">
<ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p><code class="literal">BACKUP</code></p></li><li class="listitem"><p><code class="literal">BEFORE</code> (R)</p></li><li class="listitem"><p><code class="literal">BEGIN</code></p></li><li class="listitem"><p><code class="literal">BETWEEN</code> (R)</p></li><li class="listitem"><p><code class="literal">BIGINT</code> (R)</p></li><li class="listitem"><p><code class="literal">BINARY</code> (R)</p></li><li class="listitem"><p><code class="literal">BINLOG</code></p></li><li class="listitem"><p><code class="literal">BIT</code></p></li><li class="listitem"><p><code class="literal">BLOB</code> (R)</p></li><li class="listitem"><p><code class="literal">BLOCK</code></p></li><li class="listitem"><p><code class="literal">BOOL</code></p></li><li class="listitem"><p><code class="literal">BOOLEAN</code></p></li><li class="listitem"><p><code class="literal">BOTH</code> (R)</p></li><li class="listitem"><p><code class="literal">BTREE</code></p></li><li class="listitem"><p><code class="literal">BUCKETS</code>; added in 8.0.2 (nonreserved)</p></li><li class="listitem"><p><code class="literal">BY</code> (R)</p></li><li class="listitem"><p><code class="literal">BYTE</code></p></li></ul>
</div>
<p><a name="keywords-8-0-detailed-C"></a>C</p>
<div class="itemizedlist">
<ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p><code class="literal">CACHE</code></p></li><li class="listitem"><p><code class="literal">CALL</code> (R)</p></li><li class="listitem"><p><code class="literal">CASCADE</code> (R)</p></li><li class="listitem"><p><code class="literal">CASCADED</code></p></li><li class="listitem"><p><code class="literal">CASE</code> (R)</p></li><li class="listitem"><p><code class="literal">CATALOG_NAME</code></p></li><li class="listitem"><p><code class="literal">CHAIN</code></p></li><li class="listitem"><p><code class="literal">CHANGE</code> (R)</p></li><li class="listitem"><p><code class="literal">CHANGED</code></p></li><li class="listitem"><p><code class="literal">CHANNEL</code></p></li><li class="listitem"><p><code class="literal">CHAR</code> (R)</p></li><li class="listitem"><p><code class="literal">CHARACTER</code> (R)</p></li><li class="listitem"><p><code class="literal">CHARSET</code></p></li><li class="listitem"><p><code class="literal">CHECK</code> (R)</p></li><li class="listitem"><p><code class="literal">CHECKSUM</code></p></li><li class="listitem"><p><code class="literal">CIPHER</code></p></li><li class="listitem"><p><code class="literal">CLASS_ORIGIN</code></p></li><li class="listitem"><p><code class="literal">CLIENT</code></p></li><li class="listitem"><p><code class="literal">CLONE</code>; added in 8.0.3 (nonreserved)</p></li><li class="listitem"><p><code class="literal">CLOSE</code></p></li><li class="listitem"><p><code class="literal">COALESCE</code></p></li><li class="listitem"><p><code class="literal">CODE</code></p></li><li class="listitem"><p><code class="literal">COLLATE</code> (R)</p></li><li class="listitem"><p><code class="literal">COLLATION</code></p></li><li class="listitem"><p><code class="literal">COLUMN</code> (R)</p></li><li class="listitem"><p><code class="literal">COLUMNS</code></p></li><li class="listitem"><p><code class="literal">COLUMN_FORMAT</code></p></li><li class="listitem"><p><code class="literal">COLUMN_NAME</code></p></li><li class="listitem"><p><code class="literal">COMMENT</code></p></li><li class="listitem"><p><code class="literal">COMMIT</code></p></li><li class="listitem"><p><code class="literal">COMMITTED</code></p></li><li class="listitem"><p><code class="literal">COMPACT</code></p></li><li class="listitem"><p><code class="literal">COMPLETION</code></p></li><li class="listitem"><p><code class="literal">COMPONENT</code></p></li><li class="listitem"><p><code class="literal">COMPRESSED</code></p></li><li class="listitem"><p><code class="literal">COMPRESSION</code></p></li><li class="listitem"><p><code class="literal">CONCURRENT</code></p></li><li class="listitem"><p><code class="literal">CONDITION</code> (R)</p></li><li class="listitem"><p><code class="literal">CONNECTION</code></p></li><li class="listitem"><p><code class="literal">CONSISTENT</code></p></li><li class="listitem"><p><code class="literal">CONSTRAINT</code> (R)</p></li><li class="listitem"><p><code class="literal">CONSTRAINT_CATALOG</code></p></li><li class="listitem"><p><code class="literal">CONSTRAINT_NAME</code></p></li><li class="listitem"><p><code class="literal">CONSTRAINT_SCHEMA</code></p></li><li class="listitem"><p><code class="literal">CONTAINS</code></p></li><li class="listitem"><p><code class="literal">CONTEXT</code></p></li><li class="listitem"><p><code class="literal">CONTINUE</code> (R)</p></li><li class="listitem"><p><code class="literal">CONVERT</code> (R)</p></li><li class="listitem"><p><code class="literal">CPU</code></p></li><li class="listitem"><p><code class="literal">CREATE</code> (R)</p></li><li class="listitem"><p><code class="literal">CROSS</code> (R)</p></li><li class="listitem"><p><code class="literal">CUBE</code> (R); became reserved in 8.0.1</p></li><li class="listitem"><p><code class="literal">CUME_DIST</code> (R); added in 8.0.2 (reserved)</p></li><li class="listitem"><p><code class="literal">CURRENT</code></p></li><li class="listitem"><p><code class="literal">CURRENT_DATE</code> (R)</p></li><li class="listitem"><p><code class="literal">CURRENT_TIME</code> (R)</p></li><li class="listitem"><p><code class="literal">CURRENT_TIMESTAMP</code> (R)</p></li><li class="listitem"><p><code class="literal">CURRENT_USER</code> (R)</p></li><li class="listitem"><p><code class="literal">CURSOR</code> (R)</p></li><li class="listitem"><p><code class="literal">CURSOR_NAME</code></p></li></ul>
</div>
<p><a name="keywords-8-0-detailed-D"></a>D</p>
<div class="itemizedlist">
<ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p><code class="literal">DATA</code></p></li><li class="listitem"><p><code class="literal">DATABASE</code> (R)</p></li><li class="listitem"><p><code class="literal">DATABASES</code> (R)</p></li><li class="listitem"><p><code class="literal">DATAFILE</code></p></li><li class="listitem"><p><code class="literal">DATE</code></p></li><li class="listitem"><p><code class="literal">DATETIME</code></p></li><li class="listitem"><p><code class="literal">DAY</code></p></li><li class="listitem"><p><code class="literal">DAY_HOUR</code> (R)</p></li><li class="listitem"><p><code class="literal">DAY_MICROSECOND</code> (R)</p></li><li class="listitem"><p><code class="literal">DAY_MINUTE</code> (R)</p></li><li class="listitem"><p><code class="literal">DAY_SECOND</code> (R)</p></li><li class="listitem"><p><code class="literal">DEALLOCATE</code></p></li><li class="listitem"><p><code class="literal">DEC</code> (R)</p></li><li class="listitem"><p><code class="literal">DECIMAL</code> (R)</p></li><li class="listitem"><p><code class="literal">DECLARE</code> (R)</p></li><li class="listitem"><p><code class="literal">DEFAULT</code> (R)</p></li><li class="listitem"><p><code class="literal">DEFAULT_AUTH</code></p></li><li class="listitem"><p><code class="literal">DEFINER</code></p></li><li class="listitem"><p><code class="literal">DEFINITION</code>; added in 8.0.4 (nonreserved)</p></li><li class="listitem"><p><code class="literal">DELAYED</code> (R)</p></li><li class="listitem"><p><code class="literal">DELAY_KEY_WRITE</code></p></li><li class="listitem"><p><code class="literal">DELETE</code> (R)</p></li><li class="listitem"><p><code class="literal">DENSE_RANK</code> (R); added in 8.0.2 (reserved)</p></li><li class="listitem"><p><code class="literal">DESC</code> (R)</p></li><li class="listitem"><p><code class="literal">DESCRIBE</code> (R)</p></li><li class="listitem"><p><code class="literal">DESCRIPTION</code>; added in 8.0.4 (nonreserved)</p></li><li class="listitem"><p><code class="literal">DES_KEY_FILE</code>; removed in 8.0.3</p></li><li class="listitem"><p><code class="literal">DETERMINISTIC</code> (R)</p></li><li class="listitem"><p><code class="literal">DIAGNOSTICS</code></p></li><li class="listitem"><p><code class="literal">DIRECTORY</code></p></li><li class="listitem"><p><code class="literal">DISABLE</code></p></li><li class="listitem"><p><code class="literal">DISCARD</code></p></li><li class="listitem"><p><code class="literal">DISK</code></p></li><li class="listitem"><p><code class="literal">DISTINCT</code> (R)</p></li><li class="listitem"><p><code class="literal">DISTINCTROW</code> (R)</p></li><li class="listitem"><p><code class="literal">DIV</code> (R)</p></li><li class="listitem"><p><code class="literal">DO</code></p></li><li class="listitem"><p><code class="literal">DOUBLE</code> (R)</p></li><li class="listitem"><p><code class="literal">DROP</code> (R)</p></li><li class="listitem"><p><code class="literal">DUAL</code> (R)</p></li><li class="listitem"><p><code class="literal">DUMPFILE</code></p></li><li class="listitem"><p><code class="literal">DUPLICATE</code></p></li><li class="listitem"><p><code class="literal">DYNAMIC</code></p></li></ul>
</div>
<p><a name="keywords-8-0-detailed-E"></a>E</p>
<div class="itemizedlist">
<ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p><code class="literal">EACH</code> (R)</p></li><li class="listitem"><p><code class="literal">ELSE</code> (R)</p></li><li class="listitem"><p><code class="literal">ELSEIF</code> (R)</p></li><li class="listitem"><p><code class="literal">EMPTY</code> (R); added in 8.0.4 (reserved)</p></li><li class="listitem"><p><code class="literal">ENABLE</code></p></li><li class="listitem"><p><code class="literal">ENCLOSED</code> (R)</p></li><li class="listitem"><p><code class="literal">ENCRYPTION</code></p></li><li class="listitem"><p><code class="literal">END</code></p></li><li class="listitem"><p><code class="literal">ENDS</code></p></li><li class="listitem"><p><code class="literal">ENFORCED</code>; added in 8.0.16 (nonreserved)</p></li><li class="listitem"><p><code class="literal">ENGINE</code></p></li><li class="listitem"><p><code class="literal">ENGINES</code></p></li><li class="listitem"><p><code class="literal">ENUM</code></p></li><li class="listitem"><p><code class="literal">ERROR</code></p></li><li class="listitem"><p><code class="literal">ERRORS</code></p></li><li class="listitem"><p><code class="literal">ESCAPE</code></p></li><li class="listitem"><p><code class="literal">ESCAPED</code> (R)</p></li><li class="listitem"><p><code class="literal">EVENT</code></p></li><li class="listitem"><p><code class="literal">EVENTS</code></p></li><li class="listitem"><p><code class="literal">EVERY</code></p></li><li class="listitem"><p><code class="literal">EXCEPT</code> (R)</p></li><li class="listitem"><p><code class="literal">EXCHANGE</code></p></li><li class="listitem"><p><code class="literal">EXCLUDE</code>; added in 8.0.2 (nonreserved)</p></li><li class="listitem"><p><code class="literal">EXECUTE</code></p></li><li class="listitem"><p><code class="literal">EXISTS</code> (R)</p></li><li class="listitem"><p><code class="literal">EXIT</code> (R)</p></li><li class="listitem"><p><code class="literal">EXPANSION</code></p></li><li class="listitem"><p><code class="literal">EXPIRE</code></p></li><li class="listitem"><p><code class="literal">EXPLAIN</code> (R)</p></li><li class="listitem"><p><code class="literal">EXPORT</code></p></li><li class="listitem"><p><code class="literal">EXTENDED</code></p></li><li class="listitem"><p><code class="literal">EXTENT_SIZE</code></p></li></ul>
</div>
<p><a name="keywords-8-0-detailed-F"></a>F</p>
<div class="itemizedlist">
<ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p><code class="literal">FALSE</code> (R)</p></li><li class="listitem"><p><code class="literal">FAST</code></p></li><li class="listitem"><p><code class="literal">FAULTS</code></p></li><li class="listitem"><p><code class="literal">FETCH</code> (R)</p></li><li class="listitem"><p><code class="literal">FIELDS</code></p></li><li class="listitem"><p><code class="literal">FILE</code></p></li><li class="listitem"><p><code class="literal">FILE_BLOCK_SIZE</code></p></li><li class="listitem"><p><code class="literal">FILTER</code></p></li><li class="listitem"><p><code class="literal">FIRST</code></p></li><li class="listitem"><p><code class="literal">FIRST_VALUE</code> (R); added in 8.0.2 (reserved)</p></li><li class="listitem"><p><code class="literal">FIXED</code></p></li><li class="listitem"><p><code class="literal">FLOAT</code> (R)</p></li><li class="listitem"><p><code class="literal">FLOAT4</code> (R)</p></li><li class="listitem"><p><code class="literal">FLOAT8</code> (R)</p></li><li class="listitem"><p><code class="literal">FLUSH</code></p></li><li class="listitem"><p><code class="literal">FOLLOWING</code>; added in 8.0.2 (nonreserved)</p></li><li class="listitem"><p><code class="literal">FOLLOWS</code></p></li><li class="listitem"><p><code class="literal">FOR</code> (R)</p></li><li class="listitem"><p><code class="literal">FORCE</code> (R)</p></li><li class="listitem"><p><code class="literal">FOREIGN</code> (R)</p></li><li class="listitem"><p><code class="literal">FORMAT</code></p></li><li class="listitem"><p><code class="literal">FOUND</code></p></li><li class="listitem"><p><code class="literal">FROM</code> (R)</p></li><li class="listitem"><p><code class="literal">FULL</code></p></li><li class="listitem"><p><code class="literal">FULLTEXT</code> (R)</p></li><li class="listitem"><p><code class="literal">FUNCTION</code> (R); became reserved in 8.0.1</p></li></ul>
</div>
<p><a name="keywords-8-0-detailed-G"></a>G</p>
<div class="itemizedlist">
<ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p><code class="literal">GENERAL</code></p></li><li class="listitem"><p><code class="literal">GENERATED</code> (R)</p></li><li class="listitem"><p><code class="literal">GEOMCOLLECTION</code>; added in 8.0.11 (nonreserved)</p></li><li class="listitem"><p><code class="literal">GEOMETRY</code></p></li><li class="listitem"><p><code class="literal">GEOMETRYCOLLECTION</code></p></li><li class="listitem"><p><code class="literal">GET</code> (R)</p></li><li class="listitem"><p><code class="literal">GET_FORMAT</code></p></li><li class="listitem"><p><code class="literal">GET_MASTER_PUBLIC_KEY</code>; added in 8.0.4 (reserved); became nonreserved in 8.0.11</p></li><li class="listitem"><p><code class="literal">GLOBAL</code></p></li><li class="listitem"><p><code class="literal">GRANT</code> (R)</p></li><li class="listitem"><p><code class="literal">GRANTS</code></p></li><li class="listitem"><p><code class="literal">GROUP</code> (R)</p></li><li class="listitem"><p><code class="literal">GROUPING</code> (R); added in 8.0.1 (reserved)</p></li><li class="listitem"><p><code class="literal">GROUPS</code> (R); added in 8.0.2 (reserved)</p></li><li class="listitem"><p><code class="literal">GROUP_REPLICATION</code></p></li></ul>
</div>
<p><a name="keywords-8-0-detailed-H"></a>H</p>
<div class="itemizedlist">
<ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p><code class="literal">HANDLER</code></p></li><li class="listitem"><p><code class="literal">HASH</code></p></li><li class="listitem"><p><code class="literal">HAVING</code> (R)</p></li><li class="listitem"><p><code class="literal">HELP</code></p></li><li class="listitem"><p><code class="literal">HIGH_PRIORITY</code> (R)</p></li><li class="listitem"><p><code class="literal">HISTOGRAM</code>; added in 8.0.2 (nonreserved)</p></li><li class="listitem"><p><code class="literal">HISTORY</code>; added in 8.0.3 (nonreserved)</p></li><li class="listitem"><p><code class="literal">HOST</code></p></li><li class="listitem"><p><code class="literal">HOSTS</code></p></li><li class="listitem"><p><code class="literal">HOUR</code></p></li><li class="listitem"><p><code class="literal">HOUR_MICROSECOND</code> (R)</p></li><li class="listitem"><p><code class="literal">HOUR_MINUTE</code> (R)</p></li><li class="listitem"><p><code class="literal">HOUR_SECOND</code> (R)</p></li></ul>
</div>
<p><a name="keywords-8-0-detailed-I"></a>I</p>
<div class="itemizedlist">
<ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p><code class="literal">IDENTIFIED</code></p></li><li class="listitem"><p><code class="literal">IF</code> (R)</p></li><li class="listitem"><p><code class="literal">IGNORE</code> (R)</p></li><li class="listitem"><p><code class="literal">IGNORE_SERVER_IDS</code></p></li><li class="listitem"><p><code class="literal">IMPORT</code></p></li><li class="listitem"><p><code class="literal">IN</code> (R)</p></li><li class="listitem"><p><code class="literal">INACTIVE</code>; added in 8.0.14 (nonreserved)</p></li><li class="listitem"><p><code class="literal">INDEX</code> (R)</p></li><li class="listitem"><p><code class="literal">INDEXES</code></p></li><li class="listitem"><p><code class="literal">INFILE</code> (R)</p></li><li class="listitem"><p><code class="literal">INITIAL_SIZE</code></p></li><li class="listitem"><p><code class="literal">INNER</code> (R)</p></li><li class="listitem"><p><code class="literal">INOUT</code> (R)</p></li><li class="listitem"><p><code class="literal">INSENSITIVE</code> (R)</p></li><li class="listitem"><p><code class="literal">INSERT</code> (R)</p></li><li class="listitem"><p><code class="literal">INSERT_METHOD</code></p></li><li class="listitem"><p><code class="literal">INSTALL</code></p></li><li class="listitem"><p><code class="literal">INSTANCE</code></p></li><li class="listitem"><p><code class="literal">INT</code> (R)</p></li><li class="listitem"><p><code class="literal">INT1</code> (R)</p></li><li class="listitem"><p><code class="literal">INT2</code> (R)</p></li><li class="listitem"><p><code class="literal">INT3</code> (R)</p></li><li class="listitem"><p><code class="literal">INT4</code> (R)</p></li><li class="listitem"><p><code class="literal">INT8</code> (R)</p></li><li class="listitem"><p><code class="literal">INTEGER</code> (R)</p></li><li class="listitem"><p><code class="literal">INTERVAL</code> (R)</p></li><li class="listitem"><p><code class="literal">INTO</code> (R)</p></li><li class="listitem"><p><code class="literal">INVISIBLE</code></p></li><li class="listitem"><p><code class="literal">INVOKER</code></p></li><li class="listitem"><p><code class="literal">IO</code></p></li><li class="listitem"><p><code class="literal">IO_AFTER_GTIDS</code> (R)</p></li><li class="listitem"><p><code class="literal">IO_BEFORE_GTIDS</code> (R)</p></li><li class="listitem"><p><code class="literal">IO_THREAD</code></p></li><li class="listitem"><p><code class="literal">IPC</code></p></li><li class="listitem"><p><code class="literal">IS</code> (R)</p></li><li class="listitem"><p><code class="literal">ISOLATION</code></p></li><li class="listitem"><p><code class="literal">ISSUER</code></p></li><li class="listitem"><p><code class="literal">ITERATE</code> (R)</p></li></ul>
</div>
<p><a name="keywords-8-0-detailed-J"></a>J</p>
<div class="itemizedlist">
<ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p><code class="literal">JOIN</code> (R)</p></li><li class="listitem"><p><code class="literal">JSON</code></p></li><li class="listitem"><p><code class="literal">JSON_TABLE</code> (R); added in 8.0.4 (reserved)</p></li></ul>
</div>
<p><a name="keywords-8-0-detailed-K"></a>K</p>
<div class="itemizedlist">
<ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p><code class="literal">KEY</code> (R)</p></li><li class="listitem"><p><code class="literal">KEYS</code> (R)</p></li><li class="listitem"><p><code class="literal">KEY_BLOCK_SIZE</code></p></li><li class="listitem"><p><code class="literal">KILL</code> (R)</p></li></ul>
</div>
<p><a name="keywords-8-0-detailed-L"></a>L</p>
<div class="itemizedlist">
<ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p><code class="literal">LAG</code> (R); added in 8.0.2 (reserved)</p></li><li class="listitem"><p><code class="literal">LANGUAGE</code></p></li><li class="listitem"><p><code class="literal">LAST</code></p></li><li class="listitem"><p><code class="literal">LAST_VALUE</code> (R); added in 8.0.2 (reserved)</p></li><li class="listitem"><p><code class="literal">LATERAL</code> (R); added in 8.0.14 (reserved)</p></li><li class="listitem"><p><code class="literal">LEAD</code> (R); added in 8.0.2 (reserved)</p></li><li class="listitem"><p><code class="literal">LEADING</code> (R)</p></li><li class="listitem"><p><code class="literal">LEAVE</code> (R)</p></li><li class="listitem"><p><code class="literal">LEAVES</code></p></li><li class="listitem"><p><code class="literal">LEFT</code> (R)</p></li><li class="listitem"><p><code class="literal">LESS</code></p></li><li class="listitem"><p><code class="literal">LEVEL</code></p></li><li class="listitem"><p><code class="literal">LIKE</code> (R)</p></li><li class="listitem"><p><code class="literal">LIMIT</code> (R)</p></li><li class="listitem"><p><code class="literal">LINEAR</code> (R)</p></li><li class="listitem"><p><code class="literal">LINES</code> (R)</p></li><li class="listitem"><p><code class="literal">LINESTRING</code></p></li><li class="listitem"><p><code class="literal">LIST</code></p></li><li class="listitem"><p><code class="literal">LOAD</code> (R)</p></li><li class="listitem"><p><code class="literal">LOCAL</code></p></li><li class="listitem"><p><code class="literal">LOCALTIME</code> (R)</p></li><li class="listitem"><p><code class="literal">LOCALTIMESTAMP</code> (R)</p></li><li class="listitem"><p><code class="literal">LOCK</code> (R)</p></li><li class="listitem"><p><code class="literal">LOCKED</code>; added in 8.0.1 (nonreserved)</p></li><li class="listitem"><p><code class="literal">LOCKS</code></p></li><li class="listitem"><p><code class="literal">LOGFILE</code></p></li><li class="listitem"><p><code class="literal">LOGS</code></p></li><li class="listitem"><p><code class="literal">LONG</code> (R)</p></li><li class="listitem"><p><code class="literal">LONGBLOB</code> (R)</p></li><li class="listitem"><p><code class="literal">LONGTEXT</code> (R)</p></li><li class="listitem"><p><code class="literal">LOOP</code> (R)</p></li><li class="listitem"><p><code class="literal">LOW_PRIORITY</code> (R)</p></li></ul>
</div>
<p><a name="keywords-8-0-detailed-M"></a>M</p>
<div class="itemizedlist">
<ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p><code class="literal">MASTER</code></p></li><li class="listitem"><p><code class="literal">MASTER_AUTO_POSITION</code></p></li><li class="listitem"><p><code class="literal">MASTER_BIND</code> (R)</p></li><li class="listitem"><p><code class="literal">MASTER_CONNECT_RETRY</code></p></li><li class="listitem"><p><code class="literal">MASTER_DELAY</code></p></li><li class="listitem"><p><code class="literal">MASTER_HEARTBEAT_PERIOD</code></p></li><li class="listitem"><p><code class="literal">MASTER_HOST</code></p></li><li class="listitem"><p><code class="literal">MASTER_LOG_FILE</code></p></li><li class="listitem"><p><code class="literal">MASTER_LOG_POS</code></p></li><li class="listitem"><p><code class="literal">MASTER_PASSWORD</code></p></li><li class="listitem"><p><code class="literal">MASTER_PORT</code></p></li><li class="listitem"><p><code class="literal">MASTER_PUBLIC_KEY_PATH</code>; added in 8.0.4 (nonreserved)</p></li><li class="listitem"><p><code class="literal">MASTER_RETRY_COUNT</code></p></li><li class="listitem"><p><code class="literal">MASTER_SERVER_ID</code></p></li><li class="listitem"><p><code class="literal">MASTER_SSL</code></p></li><li class="listitem"><p><code class="literal">MASTER_SSL_CA</code></p></li><li class="listitem"><p><code class="literal">MASTER_SSL_CAPATH</code></p></li><li class="listitem"><p><code class="literal">MASTER_SSL_CERT</code></p></li><li class="listitem"><p><code class="literal">MASTER_SSL_CIPHER</code></p></li><li class="listitem"><p><code class="literal">MASTER_SSL_CRL</code></p></li><li class="listitem"><p><code class="literal">MASTER_SSL_CRLPATH</code></p></li><li class="listitem"><p><code class="literal">MASTER_SSL_KEY</code></p></li><li class="listitem"><p><code class="literal">MASTER_SSL_VERIFY_SERVER_CERT</code> (R)</p></li><li class="listitem"><p><code class="literal">MASTER_TLS_VERSION</code></p></li><li class="listitem"><p><code class="literal">MASTER_USER</code></p></li><li class="listitem"><p><code class="literal">MATCH</code> (R)</p></li><li class="listitem"><p><code class="literal">MAXVALUE</code> (R)</p></li><li class="listitem"><p><code class="literal">MAX_CONNECTIONS_PER_HOUR</code></p></li><li class="listitem"><p><code class="literal">MAX_QUERIES_PER_HOUR</code></p></li><li class="listitem"><p><code class="literal">MAX_ROWS</code></p></li><li class="listitem"><p><code class="literal">MAX_SIZE</code></p></li><li class="listitem"><p><code class="literal">MAX_UPDATES_PER_HOUR</code></p></li><li class="listitem"><p><code class="literal">MAX_USER_CONNECTIONS</code></p></li><li class="listitem"><p><code class="literal">MEDIUM</code></p></li><li class="listitem"><p><code class="literal">MEDIUMBLOB</code> (R)</p></li><li class="listitem"><p><code class="literal">MEDIUMINT</code> (R)</p></li><li class="listitem"><p><code class="literal">MEDIUMTEXT</code> (R)</p></li><li class="listitem"><p><code class="literal">MEMBER</code> (R); added in 8.0.17 (reserved)</p></li><li class="listitem"><p><code class="literal">MEMORY</code></p></li><li class="listitem"><p><code class="literal">MERGE</code></p></li><li class="listitem"><p><code class="literal">MESSAGE_TEXT</code></p></li><li class="listitem"><p><code class="literal">MICROSECOND</code></p></li><li class="listitem"><p><code class="literal">MIDDLEINT</code> (R)</p></li><li class="listitem"><p><code class="literal">MIGRATE</code></p></li><li class="listitem"><p><code class="literal">MINUTE</code></p></li><li class="listitem"><p><code class="literal">MINUTE_MICROSECOND</code> (R)</p></li><li class="listitem"><p><code class="literal">MINUTE_SECOND</code> (R)</p></li><li class="listitem"><p><code class="literal">MIN_ROWS</code></p></li><li class="listitem"><p><code class="literal">MOD</code> (R)</p></li><li class="listitem"><p><code class="literal">MODE</code></p></li><li class="listitem"><p><code class="literal">MODIFIES</code> (R)</p></li><li class="listitem"><p><code class="literal">MODIFY</code></p></li><li class="listitem"><p><code class="literal">MONTH</code></p></li><li class="listitem"><p><code class="literal">MULTILINESTRING</code></p></li><li class="listitem"><p><code class="literal">MULTIPOINT</code></p></li><li class="listitem"><p><code class="literal">MULTIPOLYGON</code></p></li><li class="listitem"><p><code class="literal">MUTEX</code></p></li><li class="listitem"><p><code class="literal">MYSQL_ERRNO</code></p></li></ul>
</div>
<p><a name="keywords-8-0-detailed-N"></a>N</p>
<div class="itemizedlist">
<ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p><code class="literal">NAME</code></p></li><li class="listitem"><p><code class="literal">NAMES</code></p></li><li class="listitem"><p><code class="literal">NATIONAL</code></p></li><li class="listitem"><p><code class="literal">NATURAL</code> (R)</p></li><li class="listitem"><p><code class="literal">NCHAR</code></p></li><li class="listitem"><p><code class="literal">NDB</code></p></li><li class="listitem"><p><code class="literal">NDBCLUSTER</code></p></li><li class="listitem"><p><code class="literal">NESTED</code>; added in 8.0.4 (nonreserved)</p></li><li class="listitem"><p><code class="literal">NETWORK_NAMESPACE</code>; added in 8.0.16 (nonreserved)</p></li><li class="listitem"><p><code class="literal">NEVER</code></p></li><li class="listitem"><p><code class="literal">NEW</code></p></li><li class="listitem"><p><code class="literal">NEXT</code></p></li><li class="listitem"><p><code class="literal">NO</code></p></li><li class="listitem"><p><code class="literal">NODEGROUP</code></p></li><li class="listitem"><p><code class="literal">NONE</code></p></li><li class="listitem"><p><code class="literal">NOT</code> (R)</p></li><li class="listitem"><p><code class="literal">NOWAIT</code>; added in 8.0.1 (nonreserved)</p></li><li class="listitem"><p><code class="literal">NO_WAIT</code></p></li><li class="listitem"><p><code class="literal">NO_WRITE_TO_BINLOG</code> (R)</p></li><li class="listitem"><p><code class="literal">NTH_VALUE</code> (R); added in 8.0.2 (reserved)</p></li><li class="listitem"><p><code class="literal">NTILE</code> (R); added in 8.0.2 (reserved)</p></li><li class="listitem"><p><code class="literal">NULL</code> (R)</p></li><li class="listitem"><p><code class="literal">NULLS</code>; added in 8.0.2 (nonreserved)</p></li><li class="listitem"><p><code class="literal">NUMBER</code></p></li><li class="listitem"><p><code class="literal">NUMERIC</code> (R)</p></li><li class="listitem"><p><code class="literal">NVARCHAR</code></p></li></ul>
</div>
<p><a name="keywords-8-0-detailed-O"></a>O</p>
<div class="itemizedlist">
<ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p><code class="literal">OF</code> (R); added in 8.0.1 (reserved)</p></li><li class="listitem"><p><code class="literal">OFFSET</code></p></li><li class="listitem"><p><code class="literal">OJ</code>; added in 8.0.16 (nonreserved)</p></li><li class="listitem"><p><code class="literal">OLD</code>; added in 8.0.14 (nonreserved)</p></li><li class="listitem"><p><code class="literal">ON</code> (R)</p></li><li class="listitem"><p><code class="literal">ONE</code></p></li><li class="listitem"><p><code class="literal">ONLY</code></p></li><li class="listitem"><p><code class="literal">OPEN</code></p></li><li class="listitem"><p><code class="literal">OPTIMIZE</code> (R)</p></li><li class="listitem"><p><code class="literal">OPTIMIZER_COSTS</code> (R)</p></li><li class="listitem"><p><code class="literal">OPTION</code> (R)</p></li><li class="listitem"><p><code class="literal">OPTIONAL</code>; added in 8.0.13 (nonreserved)</p></li><li class="listitem"><p><code class="literal">OPTIONALLY</code> (R)</p></li><li class="listitem"><p><code class="literal">OPTIONS</code></p></li><li class="listitem"><p><code class="literal">OR</code> (R)</p></li><li class="listitem"><p><code class="literal">ORDER</code> (R)</p></li><li class="listitem"><p><code class="literal">ORDINALITY</code>; added in 8.0.4 (nonreserved)</p></li><li class="listitem"><p><code class="literal">ORGANIZATION</code>; added in 8.0.4 (nonreserved)</p></li><li class="listitem"><p><code class="literal">OTHERS</code>; added in 8.0.2 (nonreserved)</p></li><li class="listitem"><p><code class="literal">OUT</code> (R)</p></li><li class="listitem"><p><code class="literal">OUTER</code> (R)</p></li><li class="listitem"><p><code class="literal">OUTFILE</code> (R)</p></li><li class="listitem"><p><code class="literal">OVER</code> (R); added in 8.0.2 (reserved)</p></li><li class="listitem"><p><code class="literal">OWNER</code></p></li></ul>
</div>
<p><a name="keywords-8-0-detailed-P"></a>P</p>
<div class="itemizedlist">
<ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p><code class="literal">PACK_KEYS</code></p></li><li class="listitem"><p><code class="literal">PAGE</code></p></li><li class="listitem"><p><code class="literal">PARSER</code></p></li><li class="listitem"><p><code class="literal">PARTIAL</code></p></li><li class="listitem"><p><code class="literal">PARTITION</code> (R)</p></li><li class="listitem"><p><code class="literal">PARTITIONING</code></p></li><li class="listitem"><p><code class="literal">PARTITIONS</code></p></li><li class="listitem"><p><code class="literal">PASSWORD</code></p></li><li class="listitem"><p><code class="literal">PATH</code>; added in 8.0.4 (nonreserved)</p></li><li class="listitem"><p><code class="literal">PERCENT_RANK</code> (R); added in 8.0.2 (reserved)</p></li><li class="listitem"><p><code class="literal">PERSIST</code>; became nonreserved in 8.0.16</p></li><li class="listitem"><p><code class="literal">PERSIST_ONLY</code>; added in 8.0.2 (reserved); became nonreserved in 8.0.16</p></li><li class="listitem"><p><code class="literal">PHASE</code></p></li><li class="listitem"><p><code class="literal">PLUGIN</code></p></li><li class="listitem"><p><code class="literal">PLUGINS</code></p></li><li class="listitem"><p><code class="literal">PLUGIN_DIR</code></p></li><li class="listitem"><p><code class="literal">POINT</code></p></li><li class="listitem"><p><code class="literal">POLYGON</code></p></li><li class="listitem"><p><code class="literal">PORT</code></p></li><li class="listitem"><p><code class="literal">PRECEDES</code></p></li><li class="listitem"><p><code class="literal">PRECEDING</code>; added in 8.0.2 (nonreserved)</p></li><li class="listitem"><p><code class="literal">PRECISION</code> (R)</p></li><li class="listitem"><p><code class="literal">PREPARE</code></p></li><li class="listitem"><p><code class="literal">PRESERVE</code></p></li><li class="listitem"><p><code class="literal">PREV</code></p></li><li class="listitem"><p><code class="literal">PRIMARY</code> (R)</p></li><li class="listitem"><p><code class="literal">PRIVILEGES</code></p></li><li class="listitem"><p><code class="literal">PROCEDURE</code> (R)</p></li><li class="listitem"><p><code class="literal">PROCESS</code>; added in 8.0.11 (nonreserved)</p></li><li class="listitem"><p><code class="literal">PROCESSLIST</code></p></li><li class="listitem"><p><code class="literal">PROFILE</code></p></li><li class="listitem"><p><code class="literal">PROFILES</code></p></li><li class="listitem"><p><code class="literal">PROXY</code></p></li><li class="listitem"><p><code class="literal">PURGE</code> (R)</p></li></ul>
</div>
<p><a name="keywords-8-0-detailed-Q"></a>Q</p>
<div class="itemizedlist">
<ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p><code class="literal">QUARTER</code></p></li><li class="listitem"><p><code class="literal">QUERY</code></p></li><li class="listitem"><p><code class="literal">QUICK</code></p></li></ul>
</div>
<p><a name="keywords-8-0-detailed-R"></a>R</p>
<div class="itemizedlist">
<ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p><code class="literal">RANGE</code> (R)</p></li><li class="listitem"><p><code class="literal">RANK</code> (R); added in 8.0.2 (reserved)</p></li><li class="listitem"><p><code class="literal">READ</code> (R)</p></li><li class="listitem"><p><code class="literal">READS</code> (R)</p></li><li class="listitem"><p><code class="literal">READ_ONLY</code></p></li><li class="listitem"><p><code class="literal">READ_WRITE</code> (R)</p></li><li class="listitem"><p><code class="literal">REAL</code> (R)</p></li><li class="listitem"><p><code class="literal">REBUILD</code></p></li><li class="listitem"><p><code class="literal">RECOVER</code></p></li><li class="listitem"><p><code class="literal">RECURSIVE</code> (R); added in 8.0.1 (reserved)</p></li><li class="listitem"><p><code class="literal">REDOFILE</code>; removed in 8.0.3</p></li><li class="listitem"><p><code class="literal">REDO_BUFFER_SIZE</code></p></li><li class="listitem"><p><code class="literal">REDUNDANT</code></p></li><li class="listitem"><p><code class="literal">REFERENCE</code>; added in 8.0.4 (nonreserved)</p></li><li class="listitem"><p><code class="literal">REFERENCES</code> (R)</p></li><li class="listitem"><p><code class="literal">REGEXP</code> (R)</p></li><li class="listitem"><p><code class="literal">RELAY</code></p></li><li class="listitem"><p><code class="literal">RELAYLOG</code></p></li><li class="listitem"><p><code class="literal">RELAY_LOG_FILE</code></p></li><li class="listitem"><p><code class="literal">RELAY_LOG_POS</code></p></li><li class="listitem"><p><code class="literal">RELAY_THREAD</code></p></li><li class="listitem"><p><code class="literal">RELEASE</code> (R)</p></li><li class="listitem"><p><code class="literal">RELOAD</code></p></li><li class="listitem"><p><code class="literal">REMOTE</code>; added in 8.0.3 (nonreserved); removed in 8.0.14</p></li><li class="listitem"><p><code class="literal">REMOVE</code></p></li><li class="listitem"><p><code class="literal">RENAME</code> (R)</p></li><li class="listitem"><p><code class="literal">REORGANIZE</code></p></li><li class="listitem"><p><code class="literal">REPAIR</code></p></li><li class="listitem"><p><code class="literal">REPEAT</code> (R)</p></li><li class="listitem"><p><code class="literal">REPEATABLE</code></p></li><li class="listitem"><p><code class="literal">REPLACE</code> (R)</p></li><li class="listitem"><p><code class="literal">REPLICATE_DO_DB</code></p></li><li class="listitem"><p><code class="literal">REPLICATE_DO_TABLE</code></p></li><li class="listitem"><p><code class="literal">REPLICATE_IGNORE_DB</code></p></li><li class="listitem"><p><code class="literal">REPLICATE_IGNORE_TABLE</code></p></li><li class="listitem"><p><code class="literal">REPLICATE_REWRITE_DB</code></p></li><li class="listitem"><p><code class="literal">REPLICATE_WILD_DO_TABLE</code></p></li><li class="listitem"><p><code class="literal">REPLICATE_WILD_IGNORE_TABLE</code></p></li><li class="listitem"><p><code class="literal">REPLICATION</code></p></li><li class="listitem"><p><code class="literal">REQUIRE</code> (R)</p></li><li class="listitem"><p><code class="literal">RESET</code></p></li><li class="listitem"><p><code class="literal">RESIGNAL</code> (R)</p></li><li class="listitem"><p><code class="literal">RESOURCE</code>; added in 8.0.3 (nonreserved)</p></li><li class="listitem"><p><code class="literal">RESPECT</code>; added in 8.0.2 (nonreserved)</p></li><li class="listitem"><p><code class="literal">RESTART</code>; added in 8.0.4 (nonreserved)</p></li><li class="listitem"><p><code class="literal">RESTORE</code></p></li><li class="listitem"><p><code class="literal">RESTRICT</code> (R)</p></li><li class="listitem"><p><code class="literal">RESUME</code></p></li><li class="listitem"><p><code class="literal">RETAIN</code>; added in 8.0.14 (nonreserved)</p></li><li class="listitem"><p><code class="literal">RETURN</code> (R)</p></li><li class="listitem"><p><code class="literal">RETURNED_SQLSTATE</code></p></li><li class="listitem"><p><code class="literal">RETURNS</code></p></li><li class="listitem"><p><code class="literal">REUSE</code>; added in 8.0.3 (nonreserved)</p></li><li class="listitem"><p><code class="literal">REVERSE</code></p></li><li class="listitem"><p><code class="literal">REVOKE</code> (R)</p></li><li class="listitem"><p><code class="literal">RIGHT</code> (R)</p></li><li class="listitem"><p><code class="literal">RLIKE</code> (R)</p></li><li class="listitem"><p><code class="literal">ROLE</code>; became nonreserved in 8.0.1</p></li><li class="listitem"><p><code class="literal">ROLLBACK</code></p></li><li class="listitem"><p><code class="literal">ROLLUP</code></p></li><li class="listitem"><p><code class="literal">ROTATE</code></p></li><li class="listitem"><p><code class="literal">ROUTINE</code></p></li><li class="listitem"><p><code class="literal">ROW</code> (R); became reserved in 8.0.2</p></li><li class="listitem"><p><code class="literal">ROWS</code> (R); became reserved in 8.0.2</p></li><li class="listitem"><p><code class="literal">ROW_COUNT</code></p></li><li class="listitem"><p><code class="literal">ROW_FORMAT</code></p></li><li class="listitem"><p><code class="literal">ROW_NUMBER</code> (R); added in 8.0.2 (reserved)</p></li><li class="listitem"><p><code class="literal">RTREE</code></p></li></ul>
</div>
<p><a name="keywords-8-0-detailed-S"></a>S</p>
<div class="itemizedlist">
<ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p><code class="literal">SAVEPOINT</code></p></li><li class="listitem"><p><code class="literal">SCHEDULE</code></p></li><li class="listitem"><p><code class="literal">SCHEMA</code> (R)</p></li><li class="listitem"><p><code class="literal">SCHEMAS</code> (R)</p></li><li class="listitem"><p><code class="literal">SCHEMA_NAME</code></p></li><li class="listitem"><p><code class="literal">SECOND</code></p></li><li class="listitem"><p><code class="literal">SECONDARY</code>; added in 8.0.16 (nonreserved)</p></li><li class="listitem"><p><code class="literal">SECONDARY_ENGINE</code>; added in 8.0.13 (nonreserved)</p></li><li class="listitem"><p><code class="literal">SECONDARY_LOAD</code>; added in 8.0.13 (nonreserved)</p></li><li class="listitem"><p><code class="literal">SECONDARY_UNLOAD</code>; added in 8.0.13 (nonreserved)</p></li><li class="listitem"><p><code class="literal">SECOND_MICROSECOND</code> (R)</p></li><li class="listitem"><p><code class="literal">SECURITY</code></p></li><li class="listitem"><p><code class="literal">SELECT</code> (R)</p></li><li class="listitem"><p><code class="literal">SENSITIVE</code> (R)</p></li><li class="listitem"><p><code class="literal">SEPARATOR</code> (R)</p></li><li class="listitem"><p><code class="literal">SERIAL</code></p></li><li class="listitem"><p><code class="literal">SERIALIZABLE</code></p></li><li class="listitem"><p><code class="literal">SERVER</code></p></li><li class="listitem"><p><code class="literal">SESSION</code></p></li><li class="listitem"><p><code class="literal">SET</code> (R)</p></li><li class="listitem"><p><code class="literal">SHARE</code></p></li><li class="listitem"><p><code class="literal">SHOW</code> (R)</p></li><li class="listitem"><p><code class="literal">SHUTDOWN</code></p></li><li class="listitem"><p><code class="literal">SIGNAL</code> (R)</p></li><li class="listitem"><p><code class="literal">SIGNED</code></p></li><li class="listitem"><p><code class="literal">SIMPLE</code></p></li><li class="listitem"><p><code class="literal">SKIP</code>; added in 8.0.1 (nonreserved)</p></li><li class="listitem"><p><code class="literal">SLAVE</code></p></li><li class="listitem"><p><code class="literal">SLOW</code></p></li><li class="listitem"><p><code class="literal">SMALLINT</code> (R)</p></li><li class="listitem"><p><code class="literal">SNAPSHOT</code></p></li><li class="listitem"><p><code class="literal">SOCKET</code></p></li><li class="listitem"><p><code class="literal">SOME</code></p></li><li class="listitem"><p><code class="literal">SONAME</code></p></li><li class="listitem"><p><code class="literal">SOUNDS</code></p></li><li class="listitem"><p><code class="literal">SOURCE</code></p></li><li class="listitem"><p><code class="literal">SPATIAL</code> (R)</p></li><li class="listitem"><p><code class="literal">SPECIFIC</code> (R)</p></li><li class="listitem"><p><code class="literal">SQL</code> (R)</p></li><li class="listitem"><p><code class="literal">SQLEXCEPTION</code> (R)</p></li><li class="listitem"><p><code class="literal">SQLSTATE</code> (R)</p></li><li class="listitem"><p><code class="literal">SQLWARNING</code> (R)</p></li><li class="listitem"><p><code class="literal">SQL_AFTER_GTIDS</code></p></li><li class="listitem"><p><code class="literal">SQL_AFTER_MTS_GAPS</code></p></li><li class="listitem"><p><code class="literal">SQL_BEFORE_GTIDS</code></p></li><li class="listitem"><p><code class="literal">SQL_BIG_RESULT</code> (R)</p></li><li class="listitem"><p><code class="literal">SQL_BUFFER_RESULT</code></p></li><li class="listitem"><p><code class="literal">SQL_CACHE</code>; removed in 8.0.3</p></li><li class="listitem"><p><code class="literal">SQL_CALC_FOUND_ROWS</code> (R)</p></li><li class="listitem"><p><code class="literal">SQL_NO_CACHE</code></p></li><li class="listitem"><p><code class="literal">SQL_SMALL_RESULT</code> (R)</p></li><li class="listitem"><p><code class="literal">SQL_THREAD</code></p></li><li class="listitem"><p><code class="literal">SQL_TSI_DAY</code></p></li><li class="listitem"><p><code class="literal">SQL_TSI_HOUR</code></p></li><li class="listitem"><p><code class="literal">SQL_TSI_MINUTE</code></p></li><li class="listitem"><p><code class="literal">SQL_TSI_MONTH</code></p></li><li class="listitem"><p><code class="literal">SQL_TSI_QUARTER</code></p></li><li class="listitem"><p><code class="literal">SQL_TSI_SECOND</code></p></li><li class="listitem"><p><code class="literal">SQL_TSI_WEEK</code></p></li><li class="listitem"><p><code class="literal">SQL_TSI_YEAR</code></p></li><li class="listitem"><p><code class="literal">SRID</code>; added in 8.0.3 (nonreserved)</p></li><li class="listitem"><p><code class="literal">SSL</code> (R)</p></li><li class="listitem"><p><code class="literal">STACKED</code></p></li><li class="listitem"><p><code class="literal">START</code></p></li><li class="listitem"><p><code class="literal">STARTING</code> (R)</p></li><li class="listitem"><p><code class="literal">STARTS</code></p></li><li class="listitem"><p><code class="literal">STATS_AUTO_RECALC</code></p></li><li class="listitem"><p><code class="literal">STATS_PERSISTENT</code></p></li><li class="listitem"><p><code class="literal">STATS_SAMPLE_PAGES</code></p></li><li class="listitem"><p><code class="literal">STATUS</code></p></li><li class="listitem"><p><code class="literal">STOP</code></p></li><li class="listitem"><p><code class="literal">STORAGE</code></p></li><li class="listitem"><p><code class="literal">STORED</code> (R)</p></li><li class="listitem"><p><code class="literal">STRAIGHT_JOIN</code> (R)</p></li><li class="listitem"><p><code class="literal">STRING</code></p></li><li class="listitem"><p><code class="literal">SUBCLASS_ORIGIN</code></p></li><li class="listitem"><p><code class="literal">SUBJECT</code></p></li><li class="listitem"><p><code class="literal">SUBPARTITION</code></p></li><li class="listitem"><p><code class="literal">SUBPARTITIONS</code></p></li><li class="listitem"><p><code class="literal">SUPER</code></p></li><li class="listitem"><p><code class="literal">SUSPEND</code></p></li><li class="listitem"><p><code class="literal">SWAPS</code></p></li><li class="listitem"><p><code class="literal">SWITCHES</code></p></li><li class="listitem"><p><code class="literal">SYSTEM</code> (R); added in 8.0.3 (reserved)</p></li></ul>
</div>
<p><a name="keywords-8-0-detailed-T"></a>T</p>
<div class="itemizedlist">
<ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p><code class="literal">TABLE</code> (R)</p></li><li class="listitem"><p><code class="literal">TABLES</code></p></li><li class="listitem"><p><code class="literal">TABLESPACE</code></p></li><li class="listitem"><p><code class="literal">TABLE_CHECKSUM</code></p></li><li class="listitem"><p><code class="literal">TABLE_NAME</code></p></li><li class="listitem"><p><code class="literal">TEMPORARY</code></p></li><li class="listitem"><p><code class="literal">TEMPTABLE</code></p></li><li class="listitem"><p><code class="literal">TERMINATED</code> (R)</p></li><li class="listitem"><p><code class="literal">TEXT</code></p></li><li class="listitem"><p><code class="literal">THAN</code></p></li><li class="listitem"><p><code class="literal">THEN</code> (R)</p></li><li class="listitem"><p><code class="literal">THREAD_PRIORITY</code>; added in 8.0.3 (nonreserved)</p></li><li class="listitem"><p><code class="literal">TIES</code>; added in 8.0.2 (nonreserved)</p></li><li class="listitem"><p><code class="literal">TIME</code></p></li><li class="listitem"><p><code class="literal">TIMESTAMP</code></p></li><li class="listitem"><p><code class="literal">TIMESTAMPADD</code></p></li><li class="listitem"><p><code class="literal">TIMESTAMPDIFF</code></p></li><li class="listitem"><p><code class="literal">TINYBLOB</code> (R)</p></li><li class="listitem"><p><code class="literal">TINYINT</code> (R)</p></li><li class="listitem"><p><code class="literal">TINYTEXT</code> (R)</p></li><li class="listitem"><p><code class="literal">TO</code> (R)</p></li><li class="listitem"><p><code class="literal">TRAILING</code> (R)</p></li><li class="listitem"><p><code class="literal">TRANSACTION</code></p></li><li class="listitem"><p><code class="literal">TRIGGER</code> (R)</p></li><li class="listitem"><p><code class="literal">TRIGGERS</code></p></li><li class="listitem"><p><code class="literal">TRUE</code> (R)</p></li><li class="listitem"><p><code class="literal">TRUNCATE</code></p></li><li class="listitem"><p><code class="literal">TYPE</code></p></li><li class="listitem"><p><code class="literal">TYPES</code></p></li></ul>
</div>
<p><a name="keywords-8-0-detailed-U"></a>U</p>
<div class="itemizedlist">
<ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p><code class="literal">UNBOUNDED</code>; added in 8.0.2 (nonreserved)</p></li><li class="listitem"><p><code class="literal">UNCOMMITTED</code></p></li><li class="listitem"><p><code class="literal">UNDEFINED</code></p></li><li class="listitem"><p><code class="literal">UNDO</code> (R)</p></li><li class="listitem"><p><code class="literal">UNDOFILE</code></p></li><li class="listitem"><p><code class="literal">UNDO_BUFFER_SIZE</code></p></li><li class="listitem"><p><code class="literal">UNICODE</code></p></li><li class="listitem"><p><code class="literal">UNINSTALL</code></p></li><li class="listitem"><p><code class="literal">UNION</code> (R)</p></li><li class="listitem"><p><code class="literal">UNIQUE</code> (R)</p></li><li class="listitem"><p><code class="literal">UNKNOWN</code></p></li><li class="listitem"><p><code class="literal">UNLOCK</code> (R)</p></li><li class="listitem"><p><code class="literal">UNSIGNED</code> (R)</p></li><li class="listitem"><p><code class="literal">UNTIL</code></p></li><li class="listitem"><p><code class="literal">UPDATE</code> (R)</p></li><li class="listitem"><p><code class="literal">UPGRADE</code></p></li><li class="listitem"><p><code class="literal">USAGE</code> (R)</p></li><li class="listitem"><p><code class="literal">USE</code> (R)</p></li><li class="listitem"><p><code class="literal">USER</code></p></li><li class="listitem"><p><code class="literal">USER_RESOURCES</code></p></li><li class="listitem"><p><code class="literal">USE_FRM</code></p></li><li class="listitem"><p><code class="literal">USING</code> (R)</p></li><li class="listitem"><p><code class="literal">UTC_DATE</code> (R)</p></li><li class="listitem"><p><code class="literal">UTC_TIME</code> (R)</p></li><li class="listitem"><p><code class="literal">UTC_TIMESTAMP</code> (R)</p></li></ul>
</div>
<p><a name="keywords-8-0-detailed-V"></a>V</p>
<div class="itemizedlist">
<ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p><code class="literal">VALIDATION</code></p></li><li class="listitem"><p><code class="literal">VALUE</code></p></li><li class="listitem"><p><code class="literal">VALUES</code> (R)</p></li><li class="listitem"><p><code class="literal">VARBINARY</code> (R)</p></li><li class="listitem"><p><code class="literal">VARCHAR</code> (R)</p></li><li class="listitem"><p><code class="literal">VARCHARACTER</code> (R)</p></li><li class="listitem"><p><code class="literal">VARIABLES</code></p></li><li class="listitem"><p><code class="literal">VARYING</code> (R)</p></li><li class="listitem"><p><code class="literal">VCPU</code>; added in 8.0.3 (nonreserved)</p></li><li class="listitem"><p><code class="literal">VIEW</code></p></li><li class="listitem"><p><code class="literal">VIRTUAL</code> (R)</p></li><li class="listitem"><p><code class="literal">VISIBLE</code></p></li></ul>
</div>
<p><a name="keywords-8-0-detailed-W"></a>W</p>
<div class="itemizedlist">
<ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p><code class="literal">WAIT</code></p></li><li class="listitem"><p><code class="literal">WARNINGS</code></p></li><li class="listitem"><p><code class="literal">WEEK</code></p></li><li class="listitem"><p><code class="literal">WEIGHT_STRING</code></p></li><li class="listitem"><p><code class="literal">WHEN</code> (R)</p></li><li class="listitem"><p><code class="literal">WHERE</code> (R)</p></li><li class="listitem"><p><code class="literal">WHILE</code> (R)</p></li><li class="listitem"><p><code class="literal">WINDOW</code> (R); added in 8.0.2 (reserved)</p></li><li class="listitem"><p><code class="literal">WITH</code> (R)</p></li><li class="listitem"><p><code class="literal">WITHOUT</code></p></li><li class="listitem"><p><code class="literal">WORK</code></p></li><li class="listitem"><p><code class="literal">WRAPPER</code></p></li><li class="listitem"><p><code class="literal">WRITE</code> (R)</p></li></ul>
</div>
<p><a name="keywords-8-0-detailed-X"></a>X</p>
<div class="itemizedlist">
<ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p><code class="literal">X509</code></p></li><li class="listitem"><p><code class="literal">XA</code></p></li><li class="listitem"><p><code class="literal">XID</code></p></li><li class="listitem"><p><code class="literal">XML</code></p></li><li class="listitem"><p><code class="literal">XOR</code> (R)</p></li></ul>
</div>
<p><a name="keywords-8-0-detailed-Y"></a>Y</p>
<div class="itemizedlist">
<ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p><code class="literal">YEAR</code></p></li><li class="listitem"><p><code class="literal">YEAR_MONTH</code> (R)</p></li></ul>
</div>
<p><a name="keywords-8-0-detailed-Z"></a>Z</p>
<div class="itemizedlist">
<ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p><code class="literal">ZEROFILL</code> (R)</p></li></ul>
</div>

</div>

<div class="simplesect">

<div class="titlepage">
<div>

<div class="simple">
<h3 class="title"><a name="keywords-new-in-current-series"></a>MySQL 8.0 New Keywords and Reserved Words</h3>

</div>

</div>

</div>
<p>
        The following list shows the keywords and reserved words that
        are added in MySQL 8.0, compared to MySQL
        5.7. Reserved keywords are marked with (R).
      </p><p><a name="keywords-new-8-0-top"></a><a class="link" href="language-structure.html#keywords-new-8-0-A">A</a>
 | <a class="link" href="language-structure.html#keywords-new-8-0-B">B</a>
 | <a class="link" href="language-structure.html#keywords-new-8-0-C">C</a>
 | <a class="link" href="language-structure.html#keywords-new-8-0-D">D</a>
 | <a class="link" href="language-structure.html#keywords-new-8-0-E">E</a>
 | <a class="link" href="language-structure.html#keywords-new-8-0-F">F</a>
 | <a class="link" href="language-structure.html#keywords-new-8-0-G">G</a>
 | <a class="link" href="language-structure.html#keywords-new-8-0-H">H</a>
 | <a class="link" href="language-structure.html#keywords-new-8-0-I">I</a>
 | <a class="link" href="language-structure.html#keywords-new-8-0-J">J</a>
 | <a class="link" href="language-structure.html#keywords-new-8-0-L">L</a>
 | <a class="link" href="language-structure.html#keywords-new-8-0-M">M</a>
 | <a class="link" href="language-structure.html#keywords-new-8-0-N">N</a>
 | <a class="link" href="language-structure.html#keywords-new-8-0-O">O</a>
 | <a class="link" href="language-structure.html#keywords-new-8-0-P">P</a>
 | <a class="link" href="language-structure.html#keywords-new-8-0-R">R</a>
 | <a class="link" href="language-structure.html#keywords-new-8-0-S">S</a>
 | <a class="link" href="language-structure.html#keywords-new-8-0-T">T</a>
 | <a class="link" href="language-structure.html#keywords-new-8-0-U">U</a>
 | <a class="link" href="language-structure.html#keywords-new-8-0-V">V</a>
 | <a class="link" href="language-structure.html#keywords-new-8-0-W">W</a>
</p><p><a name="keywords-new-8-0-A"></a>A</p>
<div class="itemizedlist">
<ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p><code class="literal">ACTIVE</code></p></li><li class="listitem"><p><code class="literal">ADMIN</code></p></li><li class="listitem"><p><code class="literal">ARRAY</code> (R)</p></li></ul>
</div>
<p><a name="keywords-new-8-0-B"></a>B</p>
<div class="itemizedlist">
<ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p><code class="literal">BUCKETS</code></p></li></ul>
</div>
<p><a name="keywords-new-8-0-C"></a>C</p>
<div class="itemizedlist">
<ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p><code class="literal">CLONE</code></p></li><li class="listitem"><p><code class="literal">COMPONENT</code></p></li><li class="listitem"><p><code class="literal">CUME_DIST</code> (R)</p></li></ul>
</div>
<p><a name="keywords-new-8-0-D"></a>D</p>
<div class="itemizedlist">
<ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p><code class="literal">DEFINITION</code></p></li><li class="listitem"><p><code class="literal">DENSE_RANK</code> (R)</p></li><li class="listitem"><p><code class="literal">DESCRIPTION</code></p></li></ul>
</div>
<p><a name="keywords-new-8-0-E"></a>E</p>
<div class="itemizedlist">
<ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p><code class="literal">EMPTY</code> (R)</p></li><li class="listitem"><p><code class="literal">ENFORCED</code></p></li><li class="listitem"><p><code class="literal">EXCEPT</code> (R)</p></li><li class="listitem"><p><code class="literal">EXCLUDE</code></p></li></ul>
</div>
<p><a name="keywords-new-8-0-F"></a>F</p>
<div class="itemizedlist">
<ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p><code class="literal">FIRST_VALUE</code> (R)</p></li><li class="listitem"><p><code class="literal">FOLLOWING</code></p></li></ul>
</div>
<p><a name="keywords-new-8-0-G"></a>G</p>
<div class="itemizedlist">
<ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p><code class="literal">GEOMCOLLECTION</code></p></li><li class="listitem"><p><code class="literal">GET_MASTER_PUBLIC_KEY</code></p></li><li class="listitem"><p><code class="literal">GROUPING</code> (R)</p></li><li class="listitem"><p><code class="literal">GROUPS</code> (R)</p></li></ul>
</div>
<p><a name="keywords-new-8-0-H"></a>H</p>
<div class="itemizedlist">
<ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p><code class="literal">HISTOGRAM</code></p></li><li class="listitem"><p><code class="literal">HISTORY</code></p></li></ul>
</div>
<p><a name="keywords-new-8-0-I"></a>I</p>
<div class="itemizedlist">
<ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p><code class="literal">INACTIVE</code></p></li><li class="listitem"><p><code class="literal">INVISIBLE</code></p></li></ul>
</div>
<p><a name="keywords-new-8-0-J"></a>J</p>
<div class="itemizedlist">
<ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p><code class="literal">JSON_TABLE</code> (R)</p></li></ul>
</div>
<p><a name="keywords-new-8-0-L"></a>L</p>
<div class="itemizedlist">
<ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p><code class="literal">LAG</code> (R)</p></li><li class="listitem"><p><code class="literal">LAST_VALUE</code> (R)</p></li><li class="listitem"><p><code class="literal">LATERAL</code> (R)</p></li><li class="listitem"><p><code class="literal">LEAD</code> (R)</p></li><li class="listitem"><p><code class="literal">LOCKED</code></p></li></ul>
</div>
<p><a name="keywords-new-8-0-M"></a>M</p>
<div class="itemizedlist">
<ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p><code class="literal">MASTER_PUBLIC_KEY_PATH</code></p></li><li class="listitem"><p><code class="literal">MEMBER</code> (R)</p></li></ul>
</div>
<p><a name="keywords-new-8-0-N"></a>N</p>
<div class="itemizedlist">
<ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p><code class="literal">NESTED</code></p></li><li class="listitem"><p><code class="literal">NETWORK_NAMESPACE</code></p></li><li class="listitem"><p><code class="literal">NOWAIT</code></p></li><li class="listitem"><p><code class="literal">NTH_VALUE</code> (R)</p></li><li class="listitem"><p><code class="literal">NTILE</code> (R)</p></li><li class="listitem"><p><code class="literal">NULLS</code></p></li></ul>
</div>
<p><a name="keywords-new-8-0-O"></a>O</p>
<div class="itemizedlist">
<ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p><code class="literal">OF</code> (R)</p></li><li class="listitem"><p><code class="literal">OJ</code></p></li><li class="listitem"><p><code class="literal">OLD</code></p></li><li class="listitem"><p><code class="literal">OPTIONAL</code></p></li><li class="listitem"><p><code class="literal">ORDINALITY</code></p></li><li class="listitem"><p><code class="literal">ORGANIZATION</code></p></li><li class="listitem"><p><code class="literal">OTHERS</code></p></li><li class="listitem"><p><code class="literal">OVER</code> (R)</p></li></ul>
</div>
<p><a name="keywords-new-8-0-P"></a>P</p>
<div class="itemizedlist">
<ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p><code class="literal">PATH</code></p></li><li class="listitem"><p><code class="literal">PERCENT_RANK</code> (R)</p></li><li class="listitem"><p><code class="literal">PERSIST</code></p></li><li class="listitem"><p><code class="literal">PERSIST_ONLY</code></p></li><li class="listitem"><p><code class="literal">PRECEDING</code></p></li><li class="listitem"><p><code class="literal">PROCESS</code></p></li></ul>
</div>
<p><a name="keywords-new-8-0-R"></a>R</p>
<div class="itemizedlist">
<ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p><code class="literal">RANK</code> (R)</p></li><li class="listitem"><p><code class="literal">RECURSIVE</code> (R)</p></li><li class="listitem"><p><code class="literal">REFERENCE</code></p></li><li class="listitem"><p><code class="literal">RESOURCE</code></p></li><li class="listitem"><p><code class="literal">RESPECT</code></p></li><li class="listitem"><p><code class="literal">RESTART</code></p></li><li class="listitem"><p><code class="literal">RETAIN</code></p></li><li class="listitem"><p><code class="literal">REUSE</code></p></li><li class="listitem"><p><code class="literal">ROLE</code></p></li><li class="listitem"><p><code class="literal">ROW_NUMBER</code> (R)</p></li></ul>
</div>
<p><a name="keywords-new-8-0-S"></a>S</p>
<div class="itemizedlist">
<ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p><code class="literal">SECONDARY</code></p></li><li class="listitem"><p><code class="literal">SECONDARY_ENGINE</code></p></li><li class="listitem"><p><code class="literal">SECONDARY_LOAD</code></p></li><li class="listitem"><p><code class="literal">SECONDARY_UNLOAD</code></p></li><li class="listitem"><p><code class="literal">SKIP</code></p></li><li class="listitem"><p><code class="literal">SRID</code></p></li><li class="listitem"><p><code class="literal">SYSTEM</code> (R)</p></li></ul>
</div>
<p><a name="keywords-new-8-0-T"></a>T</p>
<div class="itemizedlist">
<ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p><code class="literal">THREAD_PRIORITY</code></p></li><li class="listitem"><p><code class="literal">TIES</code></p></li></ul>
</div>
<p><a name="keywords-new-8-0-U"></a>U</p>
<div class="itemizedlist">
<ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p><code class="literal">UNBOUNDED</code></p></li></ul>
</div>
<p><a name="keywords-new-8-0-V"></a>V</p>
<div class="itemizedlist">
<ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p><code class="literal">VCPU</code></p></li><li class="listitem"><p><code class="literal">VISIBLE</code></p></li></ul>
</div>
<p><a name="keywords-new-8-0-W"></a>W</p>
<div class="itemizedlist">
<ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p><code class="literal">WINDOW</code> (R)</p></li></ul>
</div>

</div>

<div class="simplesect">

<div class="titlepage">
<div>

<div class="simple">
<h3 class="title"><a name="keywords-removed-in-current-series"></a>MySQL 8.0 Removed Keywords and Reserved Words</h3>

</div>

</div>

</div>
<p>
        The following list shows the keywords and reserved words that
        are removed in MySQL 8.0, compared to MySQL
        5.7. Reserved keywords are marked with (R).
</p>
<div class="itemizedlist">
<ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p><code class="literal">ANALYSE</code></p></li><li class="listitem"><p><code class="literal">DES_KEY_FILE</code></p></li><li class="listitem"><p><code class="literal">PARSE_GCOL_EXPR</code></p></li><li class="listitem"><p><code class="literal">REDOFILE</code></p></li><li class="listitem"><p><code class="literal">SQL_CACHE</code></p></li></ul>
</div>

</div>

</div>

<div class="section">

<div class="titlepage">
<div>
<div>
<h2 class="title" style="clear: both"><a name="user-variables"></a>9.4 User-Defined Variables</h2>

</div>

</div>

</div>
<a class="indexterm" name="idm140091698690944"></a><a class="indexterm" name="idm140091698689456"></a><a class="indexterm" name="idm140091698688384"></a><a class="indexterm" name="idm140091698686896"></a><a class="indexterm" name="idm140091698685824"></a><a class="indexterm" name="idm140091698684752"></a><p>
      You can store a value in a user-defined variable in one statement
      and refer to it later in another statement. This enables you to
      pass values from one statement to another.
    </p><p>
      User variables are written as
      <code class="literal">@<em class="replaceable"><code>var_name</code></em></code>, where the
      variable name <em class="replaceable"><code>var_name</code></em> consists of
      alphanumeric characters, <code class="literal">.</code>,
      <code class="literal">_</code>, and <code class="literal">$</code>. A user variable
      name can contain other characters if you quote it as a string or
      identifier (for example, <code class="literal">@'my-var'</code>,
      <code class="literal">@"my-var"</code>, or <code class="literal">@`my-var`</code>).
    </p><p>
      User-defined variables are session specific. A user variable
      defined by one client cannot be seen or used by other clients.
      (Exception: A user with access to the Performance Schema
      <a class="link" href="performance-schema.html#performance-schema-user-variable-tables" title="26.12.10 Performance Schema User-Defined Variable Tables"><code class="literal">user_variables_by_thread</code></a> table can
      see all user variables for all sessions.) All variables for a
      given client session are automatically freed when that client
      exits.
    </p><p>
      User variable names are not case-sensitive. Names have a maximum
      length of 64 characters.
    </p><p>
      One way to set a user-defined variable is by issuing a
      <a class="link" href="sql-syntax.html#set-variable" title="13.7.5.1 SET Syntax for Variable Assignment"><code class="literal">SET</code></a>
      statement:
    </p><pre data-lang="sql" class="programlisting">
SET @<em class="replaceable"><code>var_name</code></em> = <em class="replaceable"><code>expr</code></em> [, @<em class="replaceable"><code>var_name</code></em> = <em class="replaceable"><code>expr</code></em>] ...
</pre><p>
      For <a class="link" href="sql-syntax.html#set-variable" title="13.7.5.1 SET Syntax for Variable Assignment"><code class="literal">SET</code></a>,
      either <a class="link" href="functions.html#operator_assign-equal"><code class="literal">=</code></a> or
      <a class="link" href="functions.html#operator_assign-value"><code class="literal">:=</code></a> can be
      used as the assignment operator.
    </p><p>
      User variables can be assigned a value from a limited set of data
      types: integer, decimal, floating-point, binary or nonbinary
      string, or <code class="literal">NULL</code> value. Assignment of decimal
      and real values does not preserve the precision or scale of the
      value. A value of a type other than one of the permissible types
      is converted to a permissible type. For example, a value having a
      temporal or spatial data type is converted to a binary string. A
      value having the <a class="link" href="data-types.html#json" title="11.6 The JSON Data Type"><code class="literal">JSON</code></a> data type is
      converted to a string with a character set of
      <code class="literal">utf8mb4</code> and a collation of
      <code class="literal">utf8mb4_bin</code>.
    </p><p>
      If a user variable is assigned a nonbinary (character) string
      value, it has the same character set and collation as the string.
      The coercibility of user variables is implicit. (This is the same
      coercibility as for table column values.)
    </p><p>
      Hexadecimal or bit values assigned to user variables are treated
      as binary strings. To assign a hexadecimal or bit value as a
      number to a user variable, use it in numeric context. For example,
      add 0 or use <a class="link" href="functions.html#function_cast"><code class="literal">CAST(... AS UNSIGNED)</code></a>:
    </p><pre data-lang="sql" class="programlisting">
mysql&gt; <strong class="userinput"><code>SET @v1 = X'41';</code></strong>
mysql&gt; <strong class="userinput"><code>SET @v2 = X'41'+0;</code></strong>
mysql&gt; <strong class="userinput"><code>SET @v3 = CAST(X'41' AS UNSIGNED);</code></strong>
mysql&gt; <strong class="userinput"><code>SELECT @v1, @v2, @v3;</code></strong>
+------+------+------+
| @v1  | @v2  | @v3  |
+------+------+------+
| A    |   65 |   65 |
+------+------+------+
mysql&gt; <strong class="userinput"><code>SET @v1 = b'1000001';</code></strong>
mysql&gt; <strong class="userinput"><code>SET @v2 = b'1000001'+0;</code></strong>
mysql&gt; <strong class="userinput"><code>SET @v3 = CAST(b'1000001' AS UNSIGNED);</code></strong>
mysql&gt; <strong class="userinput"><code>SELECT @v1, @v2, @v3;</code></strong>
+------+------+------+
| @v1  | @v2  | @v3  |
+------+------+------+
| A    |   65 |   65 |
+------+------+------+
</pre><p>
      If the value of a user variable is selected in a result set, it is
      returned to the client as a string.
    </p><p>
      If you refer to a variable that has not been initialized, it has a
      value of <code class="literal">NULL</code> and a type of string.
    </p><p>
      User variables may be used in most contexts where expressions are
      permitted. This does not currently include contexts that
      explicitly require a literal value, such as in the
      <code class="literal">LIMIT</code> clause of a
      <a class="link" href="sql-syntax.html#select" title="13.2.10 SELECT Syntax"><code class="literal">SELECT</code></a> statement, or the
      <code class="literal">IGNORE <em class="replaceable"><code>N</code></em> LINES</code>
      clause of a <a class="link" href="sql-syntax.html#load-data" title="13.2.7 LOAD DATA Syntax"><code class="literal">LOAD DATA</code></a> statement.
    </p><p>
      Previous releases of MySQL made it possible to assign a value to a
      user variable in statements other than
      <a class="link" href="sql-syntax.html#set-variable" title="13.7.5.1 SET Syntax for Variable Assignment"><code class="literal">SET</code></a>. This
      functionality is supported in MySQL 8.0 for backward
      compatibility but is subject to removal in a future release of
      MySQL.
    </p><p>
      When making an assignment in this way, you must use
      <a class="link" href="functions.html#operator_assign-value"><code class="literal">:=</code></a> as the
      assignment operator; <code class="literal">=</code> is treated as the
      comparison operator in statements other than
      <a class="link" href="sql-syntax.html#set-variable" title="13.7.5.1 SET Syntax for Variable Assignment"><code class="literal">SET</code></a>.
    </p><p>
      The order of evaluation for expressions involving user variables
      is undefined. For example, there is no guarantee that
      <code class="literal">SELECT @a, @a:=@a+1</code> evaluates
      <code class="literal">@a</code> first and then performs the assignment.
    </p><p>
      In addition, the default result type of a variable is based on its
      type at the beginning of the statement. This may have unintended
      effects if a variable holds a value of one type at the beginning
      of a statement in which it is also assigned a new value of a
      different type.
    </p><p>
      To avoid problems with this behavior, either do not assign a value
      to and read the value of the same variable within a single
      statement, or else set the variable to <code class="literal">0</code>,
      <code class="literal">0.0</code>, or <code class="literal">''</code> to define its
      type before you use it.
    </p><p>
      <code class="literal">HAVING</code>, <code class="literal">GROUP BY</code>, and
      <code class="literal">ORDER BY</code>, when referring to a variable that is
      assigned a value in the select expression list do not work as
      expected because the expression is evaluated on the client and
      thus can use stale column values from a previous row.
    </p><p>
      User variables are intended to provide data values. They cannot be
      used directly in an SQL statement as an identifier or as part of
      an identifier, such as in contexts where a table or database name
      is expected, or as a reserved word such as
      <a class="link" href="sql-syntax.html#select" title="13.2.10 SELECT Syntax"><code class="literal">SELECT</code></a>. This is true even if the
      variable is quoted, as shown in the following example:
    </p><pre data-lang="sql" class="programlisting">
mysql&gt; <strong class="userinput"><code>SELECT c1 FROM t;</code></strong>
+----+
| c1 |
+----+
|  0 |
+----+
|  1 |
+----+
2 rows in set (0.00 sec)

mysql&gt; <strong class="userinput"><code>SET @col = "c1";</code></strong>
Query OK, 0 rows affected (0.00 sec)

mysql&gt; <strong class="userinput"><code>SELECT @col FROM t;</code></strong>
+------+
| @col |
+------+
| c1   |
+------+
1 row in set (0.00 sec)

mysql&gt; <strong class="userinput"><code>SELECT `@col` FROM t;</code></strong>
<span class="errortext">ERROR 1054 (42S22): Unknown column '@col' in 'field list'</span>

mysql&gt; SET @col = "`c1`";
Query OK, 0 rows affected (0.00 sec)

mysql&gt; <strong class="userinput"><code>SELECT @col FROM t;</code></strong>
+------+
| @col |
+------+
| `c1` |
+------+
1 row in set (0.00 sec)
</pre><p>
      An exception to this principle that user variables cannot be used
      to provide identifiers, is when you are constructing a string for
      use as a prepared statement to execute later. In this case, user
      variables can be used to provide any part of the statement. The
      following example illustrates how this can be done:
    </p><pre data-lang="sql" class="programlisting">
mysql&gt; <strong class="userinput"><code>SET @c = "c1";</code></strong>
Query OK, 0 rows affected (0.00 sec)

mysql&gt; <strong class="userinput"><code>SET @s = CONCAT("SELECT ", @c, " FROM t");</code></strong>
Query OK, 0 rows affected (0.00 sec)

mysql&gt; <strong class="userinput"><code>PREPARE stmt FROM @s;</code></strong>
Query OK, 0 rows affected (0.04 sec)
Statement prepared

mysql&gt; <strong class="userinput"><code>EXECUTE stmt;</code></strong>
+----+
| c1 |
+----+
|  0 |
+----+
|  1 |
+----+
2 rows in set (0.00 sec)

mysql&gt; <strong class="userinput"><code>DEALLOCATE PREPARE stmt;</code></strong>
Query OK, 0 rows affected (0.00 sec)
</pre><p>
      See <a class="xref" href="sql-syntax.html#sql-syntax-prepared-statements" title="13.5 Prepared SQL Statement Syntax">Section 13.5, “Prepared SQL Statement Syntax”</a>, for more
      information.
    </p><p>
      A similar technique can be used in application programs to
      construct SQL statements using program variables, as shown here
      using PHP 5:
    </p><pre data-lang="php" class="programlisting">
&lt;?php
  $mysqli = new mysqli("localhost", "user", "pass", "test");

  if( mysqli_connect_errno() )
    die("Connection failed: %s\n", mysqli_connect_error());

  $col = "c1";

  $query = "SELECT $col FROM t";

  $result = $mysqli-&gt;query($query);

  while($row = $result-&gt;fetch_assoc())
  {
    echo "&lt;p&gt;" . $row["$col"] . "&lt;/p&gt;\n";
  }

  $result-&gt;close();

  $mysqli-&gt;close();
?&gt;
</pre><p>
      Assembling an SQL statement in this fashion is sometimes known as
      <span class="quote">“<span class="quote">Dynamic SQL</span>”</span>.
</p>
</div>
<div class="section">
<div class="titlepage">
<div>
<div>
<h2 class="title" style="clear: both"><a name="expressions"></a>9.5 Expressions</h2>

</div>

</div>

</div>
<a class="indexterm" name="idm140091698612224"></a><p>
      This section lists the grammar rules that expressions must follow
      in MySQL and provides additional information about the types of
      terms that may appear in expressions.
</p>
<div class="itemizedlist">
<ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p><a class="xref" href="language-structure.html#expression-syntax" title="Expression Syntax">Expression Syntax</a></p></li><li class="listitem"><p><a class="xref" href="language-structure.html#expression-term-notes" title="Expression Term Notes">Expression Term Notes</a></p></li><li class="listitem"><p><a class="xref" href="language-structure.html#temporal-intervals" title="Temporal Intervals">Temporal Intervals</a></p></li></ul>
</div>

<div class="simplesect">

<div class="titlepage">
<div>

<div class="simple">
<h3 class="title"><a name="expression-syntax"></a>Expression Syntax</h3>

</div>

</div>

</div>
<p>
        The following grammar rules define expression syntax in MySQL.
        The grammar shown here is based on that given in the
        <code class="filename">sql/sql_yacc.yy</code> file of MySQL source
        distributions. For additional information about some of the
        expression terms, see <a class="xref" href="language-structure.html#expression-term-notes" title="Expression Term Notes">Expression Term Notes</a>.
      </p><pre data-lang="sql" class="programlisting">
<em class="replaceable"><code>expr</code></em>:
    <em class="replaceable"><code>expr</code></em> OR <em class="replaceable"><code>expr</code></em>
  | <em class="replaceable"><code>expr</code></em> || <em class="replaceable"><code>expr</code></em>
  | <em class="replaceable"><code>expr</code></em> XOR <em class="replaceable"><code>expr</code></em>
  | <em class="replaceable"><code>expr</code></em> AND <em class="replaceable"><code>expr</code></em>
  | <em class="replaceable"><code>expr</code></em> &amp;&amp; <em class="replaceable"><code>expr</code></em>
  | NOT <em class="replaceable"><code>expr</code></em>
  | ! <em class="replaceable"><code>expr</code></em>
  | <em class="replaceable"><code>boolean_primary</code></em> IS [NOT] {TRUE | FALSE | UNKNOWN}
  | <em class="replaceable"><code>boolean_primary</code></em>

<em class="replaceable"><code>boolean_primary</code></em>:
    <em class="replaceable"><code>boolean_primary</code></em> IS [NOT] NULL
  | <em class="replaceable"><code>boolean_primary</code></em> &lt;=&gt; <em class="replaceable"><code>predicate</code></em>
  | <em class="replaceable"><code>boolean_primary</code></em> <em class="replaceable"><code>comparison_operator</code></em> <em class="replaceable"><code>predicate</code></em>
  | <em class="replaceable"><code>boolean_primary</code></em> <em class="replaceable"><code>comparison_operator</code></em> {ALL | ANY} (<em class="replaceable"><code>subquery</code></em>)
  | <em class="replaceable"><code>predicate</code></em>

<em class="replaceable"><code>comparison_operator</code></em>: = | &gt;= | &gt; | &lt;= | &lt; | &lt;&gt; | !=

<em class="replaceable"><code>predicate</code></em>:
    <em class="replaceable"><code>bit_expr</code></em> [NOT] IN (<em class="replaceable"><code>subquery</code></em>)
  | <em class="replaceable"><code>bit_expr</code></em> [NOT] IN (<em class="replaceable"><code>expr</code></em> [, <em class="replaceable"><code>expr</code></em>] ...)
  | <em class="replaceable"><code>bit_expr</code></em> [NOT] BETWEEN <em class="replaceable"><code>bit_expr</code></em> AND <em class="replaceable"><code>predicate</code></em>
  | <em class="replaceable"><code>bit_expr</code></em> SOUNDS LIKE <em class="replaceable"><code>bit_expr</code></em>
  | <em class="replaceable"><code>bit_expr</code></em> [NOT] LIKE <em class="replaceable"><code>simple_expr</code></em> [ESCAPE <em class="replaceable"><code>simple_expr</code></em>]
  | <em class="replaceable"><code>bit_expr</code></em> [NOT] REGEXP <em class="replaceable"><code>bit_expr</code></em>
  | <em class="replaceable"><code>bit_expr</code></em>

<em class="replaceable"><code>bit_expr</code></em>:
    <em class="replaceable"><code>bit_expr</code></em> | <em class="replaceable"><code>bit_expr</code></em>
  | <em class="replaceable"><code>bit_expr</code></em> &amp; <em class="replaceable"><code>bit_expr</code></em>
  | <em class="replaceable"><code>bit_expr</code></em> &lt;&lt; <em class="replaceable"><code>bit_expr</code></em>
  | <em class="replaceable"><code>bit_expr</code></em> &gt;&gt; <em class="replaceable"><code>bit_expr</code></em>
  | <em class="replaceable"><code>bit_expr</code></em> + <em class="replaceable"><code>bit_expr</code></em>
  | <em class="replaceable"><code>bit_expr</code></em> - <em class="replaceable"><code>bit_expr</code></em>
  | <em class="replaceable"><code>bit_expr</code></em> * <em class="replaceable"><code>bit_expr</code></em>
  | <em class="replaceable"><code>bit_expr</code></em> / <em class="replaceable"><code>bit_expr</code></em>
  | <em class="replaceable"><code>bit_expr</code></em> DIV <em class="replaceable"><code>bit_expr</code></em>
  | <em class="replaceable"><code>bit_expr</code></em> MOD <em class="replaceable"><code>bit_expr</code></em>
  | <em class="replaceable"><code>bit_expr</code></em> % <em class="replaceable"><code>bit_expr</code></em>
  | <em class="replaceable"><code>bit_expr</code></em> ^ <em class="replaceable"><code>bit_expr</code></em>
  | <em class="replaceable"><code>bit_expr</code></em> + <em class="replaceable"><code>interval_expr</code></em>
  | <em class="replaceable"><code>bit_expr</code></em> - <em class="replaceable"><code>interval_expr</code></em>
  | <em class="replaceable"><code>simple_expr</code></em>

<em class="replaceable"><code>simple_expr</code></em>:
    <em class="replaceable"><code>literal</code></em>
  | <em class="replaceable"><code>identifier</code></em>
  | <em class="replaceable"><code>function_call</code></em>
  | <em class="replaceable"><code>simple_expr</code></em> COLLATE <em class="replaceable"><code>collation_name</code></em>
  | <em class="replaceable"><code>param_marker</code></em>
  | <em class="replaceable"><code>variable</code></em>
  | <em class="replaceable"><code>simple_expr</code></em> || <em class="replaceable"><code>simple_expr</code></em>
  | + <em class="replaceable"><code>simple_expr</code></em>
  | - <em class="replaceable"><code>simple_expr</code></em>
  | ~ <em class="replaceable"><code>simple_expr</code></em>
  | ! <em class="replaceable"><code>simple_expr</code></em>
  | BINARY <em class="replaceable"><code>simple_expr</code></em>
  | (<em class="replaceable"><code>expr</code></em> [, <em class="replaceable"><code>expr</code></em>] ...)
  | ROW (<em class="replaceable"><code>expr</code></em>, <em class="replaceable"><code>expr</code></em> [, <em class="replaceable"><code>expr</code></em>] ...)
  | (<em class="replaceable"><code>subquery</code></em>)
  | EXISTS (<em class="replaceable"><code>subquery</code></em>)
  | {<em class="replaceable"><code>identifier</code></em> <em class="replaceable"><code>expr</code></em>}
  | <em class="replaceable"><code>match_expr</code></em>
  | <em class="replaceable"><code>case_expr</code></em>
  | <em class="replaceable"><code>interval_expr</code></em>
</pre><p>
        For operator precedence, see
        <a class="xref" href="functions.html#operator-precedence" title="12.3.1 Operator Precedence">Section 12.3.1, “Operator Precedence”</a>. The precedence and
        meaning of some operators depends on the SQL mode:
</p>
<div class="itemizedlist">
<ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>
            By default, <a class="link" href="functions.html#operator_or"><code class="literal">||</code></a>
            is a logical <a class="link" href="functions.html#operator_or"><code class="literal">OR</code></a> operator. With
            <a class="link" href="server-administration.html#sqlmode_pipes_as_concat"><code class="literal">PIPES_AS_CONCAT</code></a> enabled,
            <a class="link" href="functions.html#operator_or"><code class="literal">||</code></a> is string
            concatenation, with a precedence between
            <a class="link" href="functions.html#operator_bitwise-xor"><code class="literal">^</code></a> and
            the unary operators.
          </p></li><li class="listitem"><p>
            By default, <a class="link" href="functions.html#operator_not"><code class="literal">!</code></a>
            has a higher precedence than <code class="literal">NOT</code>. With
            <a class="link" href="server-administration.html#sqlmode_high_not_precedence"><code class="literal">HIGH_NOT_PRECEDENCE</code></a>
            enabled, <a class="link" href="functions.html#operator_not"><code class="literal">!</code></a> and
            <code class="literal">NOT</code> have the same precedence.
</p></li></ul>
</div>
<p>
        See <a class="xref" href="server-administration.html#sql-mode" title="5.1.11 Server SQL Modes">Section 5.1.11, “Server SQL Modes”</a>.
</p>
</div>
<div class="simplesect">
<div class="titlepage">
<div>
<div class="simple">
<h3 class="title"><a name="expression-term-notes"></a>Expression Term Notes</h3>

</div>

</div>

</div>
<p>
        For literal value syntax, see <a class="xref" href="language-structure.html#literals" title="9.1 Literal Values">Section 9.1, “Literal Values”</a>.
      </p><p>
        For identifier syntax, see <a class="xref" href="language-structure.html#identifiers" title="9.2 Schema Object Names">Section 9.2, “Schema Object Names”</a>.
      </p><p>
        Variables can be user variables, system variables, or stored
        program local variables or parameters:
</p>
<div class="itemizedlist">
<ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>
            User variables: <a class="xref" href="language-structure.html#user-variables" title="9.4 User-Defined Variables">Section 9.4, “User-Defined Variables”</a>
          </p></li><li class="listitem"><p>
            System variables: <a class="xref" href="server-administration.html#using-system-variables" title="5.1.9 Using System Variables">Section 5.1.9, “Using System Variables”</a>
          </p></li><li class="listitem"><p>
            Stored program local variables:
            <a class="xref" href="sql-syntax.html#declare-local-variable" title="13.6.4.1 Local Variable DECLARE Syntax">Section 13.6.4.1, “Local Variable DECLARE Syntax”</a>
          </p></li><li class="listitem"><p>
            Stored program parameters:
            <a class="xref" href="sql-syntax.html#create-procedure" title="13.1.17 CREATE PROCEDURE and CREATE FUNCTION Syntax">Section 13.1.17, “CREATE PROCEDURE and CREATE FUNCTION Syntax”</a>
</p></li></ul>
</div>
<p>
        <em class="replaceable"><code>param_marker</code></em> is <code class="literal">?</code>
        as used in prepared statements for placeholders. See
        <a class="xref" href="sql-syntax.html#prepare" title="13.5.1 PREPARE Syntax">Section 13.5.1, “PREPARE Syntax”</a>.
      </p><p>
        <code class="literal">(<em class="replaceable"><code>subquery</code></em>)</code>
        indicates a subquery that returns a single value; that is, a
        scalar subquery. See <a class="xref" href="sql-syntax.html#scalar-subqueries" title="13.2.11.1 The Subquery as Scalar Operand">Section 13.2.11.1, “The Subquery as Scalar Operand”</a>.
      </p><p>
        <code class="literal">{<em class="replaceable"><code>identifier</code></em>
        <em class="replaceable"><code>expr</code></em>}</code> is ODBC escape syntax
        and is accepted for ODBC compatibility. The value is
        <em class="replaceable"><code>expr</code></em>. The <code class="literal">{</code> and
        <code class="literal">}</code> curly braces in the syntax should be
        written literally; they are not metasyntax as used elsewhere in
        syntax descriptions.
      </p><p>
        <em class="replaceable"><code>match_expr</code></em> indicates a
        <a class="link" href="functions.html#function_match"><code class="literal">MATCH</code></a> expression. See
        <a class="xref" href="functions.html#fulltext-search" title="12.9 Full-Text Search Functions">Section 12.9, “Full-Text Search Functions”</a>.
      </p><p>
        <em class="replaceable"><code>case_expr</code></em> indicates a
        <a class="link" href="functions.html#operator_case"><code class="literal">CASE</code></a> expression. See
        <a class="xref" href="functions.html#control-flow-functions" title="12.4 Control Flow Functions">Section 12.4, “Control Flow Functions”</a>.
      </p><p>
        <em class="replaceable"><code>interval_expr</code></em> represents a temporal
        interval. See <a class="xref" href="language-structure.html#temporal-intervals" title="Temporal Intervals">Temporal Intervals</a>.
</p>
</div>
<div class="simplesect">
<div class="titlepage">
<div>
<div class="simple">
<h3 class="title"><a name="temporal-intervals"></a>Temporal Intervals</h3>

</div>

</div>

</div>
<a class="indexterm" name="idm140091698513856"></a><a class="indexterm" name="idm140091698512352"></a><a class="indexterm" name="idm140091698511264"></a><p>
        <em class="replaceable"><code>interval_expr</code></em> in expressions
        represents a temporal interval. Intervals have this syntax:
      </p><pre data-lang="sql" class="programlisting">
INTERVAL <em class="replaceable"><code>expr</code></em> <em class="replaceable"><code>unit</code></em>
</pre><p>
        <em class="replaceable"><code>expr</code></em> represents a quantity.
        <em class="replaceable"><code>unit</code></em> represents the unit for
        interpreting the quantity; it is a specifier such as
        <code class="literal">HOUR</code>, <code class="literal">DAY</code>, or
        <code class="literal">WEEK</code>. The <code class="literal">INTERVAL</code> keyword
        and the <em class="replaceable"><code>unit</code></em> specifier are not case
        sensitive.
      </p><p>
        The following table shows the expected form of the
        <em class="replaceable"><code>expr</code></em> argument for each
        <em class="replaceable"><code>unit</code></em> value.
</p>
<div class="table">
<a name="temporal-interval-arguments"></a><p class="title"><b>Table 9.2 Temporal Interval Expression and Unit Arguments</b></p>
<div class="table-contents">
<table summary="unit values and the expected expr argument for each unit value."><col width="50%"><col width="50%"><thead><tr>
            <th scope="col"><em class="replaceable"><code>unit</code></em> Value</th>
            <th scope="col">Expected <em class="replaceable"><code>expr</code></em> Format</th>
          </tr></thead><tbody><tr>
            <td scope="row"><code class="literal">MICROSECOND</code></td>
            <td><code class="literal">MICROSECONDS</code></td>
          </tr><tr>
            <td scope="row"><code class="literal">SECOND</code></td>
            <td><code class="literal">SECONDS</code></td>
          </tr><tr>
            <td scope="row"><code class="literal">MINUTE</code></td>
            <td><code class="literal">MINUTES</code></td>
          </tr><tr>
            <td scope="row"><code class="literal">HOUR</code></td>
            <td><code class="literal">HOURS</code></td>
          </tr><tr>
            <td scope="row"><code class="literal">DAY</code></td>
            <td><code class="literal">DAYS</code></td>
          </tr><tr>
            <td scope="row"><code class="literal">WEEK</code></td>
            <td><code class="literal">WEEKS</code></td>
          </tr><tr>
            <td scope="row"><code class="literal">MONTH</code></td>
            <td><code class="literal">MONTHS</code></td>
          </tr><tr>
            <td scope="row"><code class="literal">QUARTER</code></td>
            <td><code class="literal">QUARTERS</code></td>
          </tr><tr>
            <td scope="row"><code class="literal">YEAR</code></td>
            <td><code class="literal">YEARS</code></td>
          </tr><tr>
            <td scope="row"><code class="literal">SECOND_MICROSECOND</code></td>
            <td><code class="literal">'SECONDS.MICROSECONDS'</code></td>
          </tr><tr>
            <td scope="row"><code class="literal">MINUTE_MICROSECOND</code></td>
            <td><code class="literal">'MINUTES:SECONDS.MICROSECONDS'</code></td>
          </tr><tr>
            <td scope="row"><code class="literal">MINUTE_SECOND</code></td>
            <td><code class="literal">'MINUTES:SECONDS'</code></td>
          </tr><tr>
            <td scope="row"><code class="literal">HOUR_MICROSECOND</code></td>
            <td><code class="literal">'HOURS:MINUTES:SECONDS.MICROSECONDS'</code></td>
          </tr><tr>
            <td scope="row"><code class="literal">HOUR_SECOND</code></td>
            <td><code class="literal">'HOURS:MINUTES:SECONDS'</code></td>
          </tr><tr>
            <td scope="row"><code class="literal">HOUR_MINUTE</code></td>
            <td><code class="literal">'HOURS:MINUTES'</code></td>
          </tr><tr>
            <td scope="row"><code class="literal">DAY_MICROSECOND</code></td>
            <td><code class="literal">'DAYS HOURS:MINUTES:SECONDS.MICROSECONDS'</code></td>
          </tr><tr>
            <td scope="row"><code class="literal">DAY_SECOND</code></td>
            <td><code class="literal">'DAYS HOURS:MINUTES:SECONDS'</code></td>
          </tr><tr>
            <td scope="row"><code class="literal">DAY_MINUTE</code></td>
            <td><code class="literal">'DAYS HOURS:MINUTES'</code></td>
          </tr><tr>
            <td scope="row"><code class="literal">DAY_HOUR</code></td>
            <td><code class="literal">'DAYS HOURS'</code></td>
          </tr><tr>
            <td scope="row"><code class="literal">YEAR_MONTH</code></td>
            <td><code class="literal">'YEARS-MONTHS'</code></td>
</tr></tbody></table>
</div>

</div>
<br class="table-break"><p>
        MySQL permits any punctuation delimiter in the
        <em class="replaceable"><code>expr</code></em> format. Those shown in the table
        are the suggested delimiters.
      </p><p>
        Temporal intervals are used for certain functions, such as
        <a class="link" href="functions.html#function_date-add"><code class="literal">DATE_ADD()</code></a> and
        <a class="link" href="functions.html#function_date-sub"><code class="literal">DATE_SUB()</code></a>:
      </p><pre data-lang="sql" class="programlisting">
mysql&gt; <strong class="userinput"><code>SELECT DATE_ADD('2018-05-01',INTERVAL 1 DAY);</code></strong>
        -&gt; '2018-05-02'
mysql&gt; <strong class="userinput"><code>SELECT DATE_SUB('2018-05-01',INTERVAL 1 YEAR);</code></strong>
        -&gt; '2017-05-01'
mysql&gt; <strong class="userinput"><code>SELECT DATE_ADD('2020-12-31 23:59:59',</code></strong>
    -&gt;                 <strong class="userinput"><code>INTERVAL 1 SECOND);</code></strong>
        -&gt; '2021-01-01 00:00:00'
mysql&gt; <strong class="userinput"><code>SELECT DATE_ADD('2018-12-31 23:59:59',</code></strong>
    -&gt;                 <strong class="userinput"><code>INTERVAL 1 DAY);</code></strong>
        -&gt; '2019-01-01 23:59:59'
mysql&gt; <strong class="userinput"><code>SELECT DATE_ADD('2100-12-31 23:59:59',</code></strong>
    -&gt;                 <strong class="userinput"><code>INTERVAL '1:1' MINUTE_SECOND);</code></strong>
        -&gt; '2101-01-01 00:01:00'
mysql&gt; <strong class="userinput"><code>SELECT DATE_SUB('2025-01-01 00:00:00',</code></strong>
    -&gt;                 <strong class="userinput"><code>INTERVAL '1 1:1:1' DAY_SECOND);</code></strong>
        -&gt; '2024-12-30 22:58:59'
mysql&gt; <strong class="userinput"><code>SELECT DATE_ADD('1900-01-01 00:00:00',</code></strong>
    -&gt;                 <strong class="userinput"><code>INTERVAL '-1 10' DAY_HOUR);</code></strong>
        -&gt; '1899-12-30 14:00:00'
mysql&gt; <strong class="userinput"><code>SELECT DATE_SUB('1998-01-02', INTERVAL 31 DAY);</code></strong>
        -&gt; '1997-12-02'
mysql&gt; <strong class="userinput"><code>SELECT DATE_ADD('1992-12-31 23:59:59.000002',</code></strong>
    -&gt;            <strong class="userinput"><code>INTERVAL '1.999999' SECOND_MICROSECOND);</code></strong>
        -&gt; '1993-01-01 00:00:01.000001'
</pre><p>
        Temporal arithmetic also can be performed in expressions using
        <code class="literal">INTERVAL</code> together with the
        <a class="link" href="functions.html#operator_plus"><code class="literal">+</code></a> or
        <a class="link" href="functions.html#operator_minus"><code class="literal">-</code></a> operator:
      </p><pre data-lang="sql" class="programlisting">
<code class="literal">date</code> + INTERVAL <em class="replaceable"><code>expr</code></em> <em class="replaceable"><code>unit</code></em>
<code class="literal">date</code> - INTERVAL <em class="replaceable"><code>expr</code></em> <em class="replaceable"><code>unit</code></em>
</pre><p>
        <code class="literal">INTERVAL <em class="replaceable"><code>expr</code></em>
        <em class="replaceable"><code>unit</code></em></code> is permitted on either
        side of the <a class="link" href="functions.html#operator_plus"><code class="literal">+</code></a>
        operator if the expression on the other side is a date or
        datetime value. For the
        <a class="link" href="functions.html#operator_minus"><code class="literal">-</code></a> operator,
        <code class="literal">INTERVAL <em class="replaceable"><code>expr</code></em>
        <em class="replaceable"><code>unit</code></em></code> is permitted only on
        the right side, because it makes no sense to subtract a date or
        datetime value from an interval.
      </p><pre data-lang="sql" class="programlisting">
mysql&gt; <strong class="userinput"><code>SELECT '2018-12-31 23:59:59' + INTERVAL 1 SECOND;</code></strong>
        -&gt; '2019-01-01 00:00:00'
mysql&gt; <strong class="userinput"><code>SELECT INTERVAL 1 DAY + '2018-12-31';</code></strong>
        -&gt; '2019-01-01'
mysql&gt; <strong class="userinput"><code>SELECT '2025-01-01' - INTERVAL 1 SECOND;</code></strong>
        -&gt; '2024-12-31 23:59:59'
</pre><p>
        The <a class="link" href="functions.html#function_extract"><code class="literal">EXTRACT()</code></a> function uses the
        same kinds of <em class="replaceable"><code>unit</code></em> specifiers as
        <a class="link" href="functions.html#function_date-add"><code class="literal">DATE_ADD()</code></a> or
        <a class="link" href="functions.html#function_date-sub"><code class="literal">DATE_SUB()</code></a>, but extracts parts
        from the date rather than performing date arithmetic:
      </p><pre data-lang="sql" class="programlisting">
mysql&gt; <strong class="userinput"><code>SELECT EXTRACT(YEAR FROM '2019-07-02');</code></strong>
        -&gt; 2019
mysql&gt; <strong class="userinput"><code>SELECT EXTRACT(YEAR_MONTH FROM '2019-07-02 01:02:03');</code></strong>
        -&gt; 201907
</pre><p>
        Temporal intervals can be used in <a class="link" href="sql-syntax.html#create-event" title="13.1.13 CREATE EVENT Syntax"><code class="literal">CREATE
        EVENT</code></a> statements:
      </p><pre data-lang="sql" class="programlisting">
CREATE EVENT myevent
    ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 HOUR
    DO
      UPDATE myschema.mytable SET mycol = mycol + 1;
</pre><p>
        If you specify an interval value that is too short (does not
        include all the interval parts that would be expected from the
        <em class="replaceable"><code>unit</code></em> keyword), MySQL assumes that you
        have left out the leftmost parts of the interval value. For
        example, if you specify a <em class="replaceable"><code>unit</code></em> of
        <code class="literal">DAY_SECOND</code>, the value of
        <em class="replaceable"><code>expr</code></em> is expected to have days, hours,
        minutes, and seconds parts. If you specify a value like
        <code class="literal">'1:10'</code>, MySQL assumes that the days and hours
        parts are missing and the value represents minutes and seconds.
        In other words, <code class="literal">'1:10' DAY_SECOND</code> is
        interpreted in such a way that it is equivalent to
        <code class="literal">'1:10' MINUTE_SECOND</code>. This is analogous to
        the way that MySQL interprets
        <a class="link" href="data-types.html#time" title="11.3.2 The TIME Type"><code class="literal">TIME</code></a> values as representing
        elapsed time rather than as a time of day.
      </p><p>
        <em class="replaceable"><code>expr</code></em> is treated as a string, so be
        careful if you specify a nonstring value with
        <code class="literal">INTERVAL</code>. For example, with an interval
        specifier of <code class="literal">HOUR_MINUTE</code>, '6/4' is treated as
        6 hours, four minutes, whereas <code class="literal">6/4</code> evaluates
        to <code class="literal">1.5000</code> and is treated as 1 hour, 5000
        minutes:
      </p><pre data-lang="sql" class="programlisting">
mysql&gt; <strong class="userinput"><code>SELECT '6/4', 6/4;</code></strong>
        -&gt; 1.5000
mysql&gt; <strong class="userinput"><code>SELECT DATE_ADD('2019-01-01', INTERVAL '6/4' HOUR_MINUTE);</code></strong>
        -&gt; '2019-01-01 06:04:00'
mysql&gt; <strong class="userinput"><code>SELECT DATE_ADD('2019-01-01', INTERVAL 6/4 HOUR_MINUTE);</code></strong>
        -&gt; '2019-01-04 12:20:00'
</pre><p>
        To ensure interpretation of the interval value as you expect, a
        <a class="link" href="functions.html#function_cast"><code class="literal">CAST()</code></a> operation may be used. To
        treat <code class="literal">6/4</code> as 1 hour, 5 minutes, cast it to a
        <a class="link" href="data-types.html#fixed-point-types" title="11.2.2 Fixed-Point Types (Exact Value) - DECIMAL, NUMERIC"><code class="literal">DECIMAL</code></a> value with a single
        fractional digit:
      </p><pre data-lang="sql" class="programlisting">
mysql&gt; <strong class="userinput"><code>SELECT CAST(6/4 AS DECIMAL(3,1));</code></strong>
        -&gt; 1.5
mysql&gt; <strong class="userinput"><code>SELECT DATE_ADD('1970-01-01 12:00:00',</code></strong>
    -&gt;                 <strong class="userinput"><code>INTERVAL CAST(6/4 AS DECIMAL(3,1)) HOUR_MINUTE);</code></strong>
        -&gt; '1970-01-01 13:05:00'
</pre><p>
        If you add to or subtract from a date value something that
        contains a time part, the result is automatically converted to a
        datetime value:
      </p><pre data-lang="sql" class="programlisting">
mysql&gt; <strong class="userinput"><code>SELECT DATE_ADD('2023-01-01', INTERVAL 1 DAY);</code></strong>
        -&gt; '2023-01-02'
mysql&gt; <strong class="userinput"><code>SELECT DATE_ADD('2023-01-01', INTERVAL 1 HOUR);</code></strong>
        -&gt; '2023-01-01 01:00:00'
</pre><p>
        If you add <code class="literal">MONTH</code>,
        <code class="literal">YEAR_MONTH</code>, or <code class="literal">YEAR</code> and
        the resulting date has a day that is larger than the maximum day
        for the new month, the day is adjusted to the maximum days in
        the new month:
      </p><pre data-lang="sql" class="programlisting">
mysql&gt; <strong class="userinput"><code>SELECT DATE_ADD('2019-01-30', INTERVAL 1 MONTH);</code></strong>
        -&gt; '2019-02-28'
</pre><p>
        Date arithmetic operations require complete dates and do not
        work with incomplete dates such as
        <code class="literal">'2016-07-00'</code> or badly malformed dates:
      </p><pre data-lang="sql" class="programlisting">
mysql&gt; <strong class="userinput"><code>SELECT DATE_ADD('2016-07-00', INTERVAL 1 DAY);</code></strong>
        -&gt; NULL
mysql&gt; <strong class="userinput"><code>SELECT '2005-03-32' + INTERVAL 1 MONTH;</code></strong>
        -&gt; NULL
</pre>
</div>

</div>
<div class="section">
<div class="titlepage">
<div>
<div>
<h2 class="title" style="clear: both"><a name="comments"></a>9.6 Comment Syntax</h2>

</div>

</div>

</div>
<a class="indexterm" name="idm140091698351952"></a><a class="indexterm" name="idm140091698350880"></a><p>
      MySQL Server supports three comment styles:
</p>
<div class="itemizedlist">
<ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>
          From a <code class="literal">#</code> character to the end of the line.
        </p></li><li class="listitem"><p>
          From a <code class="literal">-- </code> sequence to the end of the
          line. In MySQL, the <code class="literal">-- </code> (double-dash)
          comment style requires the second dash to be followed by at
          least one whitespace or control character (such as a space,
          tab, newline, and so on). This syntax differs slightly from
          standard SQL comment syntax, as discussed in
          <a class="xref" href="introduction.html#ansi-diff-comments" title="1.8.2.4 '--' as the Start of a Comment">Section 1.8.2.4, “'--' as the Start of a Comment”</a>.
        </p></li><li class="listitem"><p>
          From a <code class="literal">/*</code> sequence to the following
          <code class="literal">*/</code> sequence, as in the C programming
          language. This syntax enables a comment to extend over
          multiple lines because the beginning and closing sequences
          need not be on the same line.
</p></li></ul>
</div>
<p>
      The following example demonstrates all three comment styles:
    </p><pre data-lang="sql" class="programlisting">
mysql&gt; <strong class="userinput"><code>SELECT 1+1;     # This comment continues to the end of line</code></strong>
mysql&gt; <strong class="userinput"><code>SELECT 1+1;     -- This comment continues to the end of line</code></strong>
mysql&gt; <strong class="userinput"><code>SELECT 1 /* this is an in-line comment */ + 1;</code></strong>
mysql&gt; <strong class="userinput"><code>SELECT 1+</code></strong>
<strong class="userinput"><code>/*</code></strong>
<strong class="userinput"><code>this is a</code></strong>
<strong class="userinput"><code>multiple-line comment</code></strong>
<strong class="userinput"><code>*/</code></strong>
<strong class="userinput"><code>1;</code></strong>
</pre><p>
      Nested comments are not supported, are deprecated, and will be
      removed in a future MySQL release. (Under some conditions, nested
      comments might be permitted, but usually are not, and users should
      avoid them.)
    </p><p>
      MySQL Server supports some variants of C-style comments. These
      enable you to write code that includes MySQL extensions, but is
      still portable, by using comments of the following form:
    </p><pre data-lang="sql" class="programlisting">
/*! <em class="replaceable"><code>MySQL-specific code</code></em> */
</pre><p>
      In this case, MySQL Server parses and executes the code within the
      comment as it would any other SQL statement, but other SQL servers
      will ignore the extensions. For example, MySQL Server recognizes
      the <code class="literal">STRAIGHT_JOIN</code> keyword in the following
      statement, but other servers will not:
    </p><pre data-lang="sql" class="programlisting">
SELECT /*! STRAIGHT_JOIN */ col1 FROM table1,table2 WHERE ...
</pre><p>
      If you add a version number after the <code class="literal">!</code>
      character, the syntax within the comment is executed only if the
      MySQL version is greater than or equal to the specified version
      number. The <code class="literal">KEY_BLOCK_SIZE</code> keyword in the
      following comment is executed only by servers from MySQL 5.1.10 or
      higher:
    </p><pre data-lang="sql" class="programlisting">
CREATE TABLE t1(a INT, KEY (a)) /*!50110 KEY_BLOCK_SIZE=1024 */;
</pre><p>
      The comment syntax just described applies to how the
      <a class="link" href="programs.html#mysqld" title="4.3.1 mysqld — The MySQL Server"><span class="command"><strong>mysqld</strong></span></a> server parses SQL statements. The
      <a class="link" href="programs.html#mysql" title="4.5.1 mysql — The MySQL Command-Line Client"><span class="command"><strong>mysql</strong></span></a> client program also performs some parsing
      of statements before sending them to the server. (It does this to
      determine statement boundaries within a multiple-statement input
      line.) For information about differences between the server and
      <a class="link" href="programs.html#mysql" title="4.5.1 mysql — The MySQL Command-Line Client"><span class="command"><strong>mysql</strong></span></a> clinet parsers, see
      <a class="xref" href="programs.html#mysql-tips" title="4.5.1.6 mysql Client Tips">Section 4.5.1.6, “mysql Client Tips”</a>.
    </p><p>
      Comments in <code class="literal">/*!12345 ... */</code> format are not
      stored on the server. If this format is used to comment stored
      programs, the comments are not retained in the program body.
    </p><p>
      Another variant of C-style comment syntax is used to specify
      optimizer hints. Hint comments include a <code class="literal">+</code>
      character following the <code class="literal">/*</code> comment opening
      sequence. Example:
    </p><pre data-lang="sql" class="programlisting">
SELECT /*+ BKA(t1) */ FROM ... ;
</pre><p>
      For more information, see <a class="xref" href="optimization.html#optimizer-hints" title="8.9.3 Optimizer Hints">Section 8.9.3, “Optimizer Hints”</a>.
    </p><p>
      The use of short-form <a class="link" href="programs.html#mysql" title="4.5.1 mysql — The MySQL Command-Line Client"><span class="command"><strong>mysql</strong></span></a> commands such as
      <code class="literal">\C</code> within multiple-line <code class="literal">/* ...
      */</code> comments is not supported.
</p>
</div>

</div>
<div class="copyright-footer">

</div>
<div class="navfooter">
<hr>
<table width="100%" summary="Navigation footer">
<tr>
<td width="40%" align="left"><a accesskey="p" href="optimization.html">Prev</a></td>
<td width="20%" align="center"><a accesskey="u" href="">Up</a></td>
<td width="40%" align="right"> <a accesskey="n" href="charset.html">Next</a></td>
</tr>
<tr>
<td width="40%" align="left" valign="top">Chapter 8 Optimization</td>
<td width="20%" align="center"><a accesskey="h" href="index.html">Home</a></td>
<td width="40%" align="right" valign="top">Chapter 10 Character Sets, Collations, Unicode</td>
</tr>
</table>
</div>
</body>
</html>
